| View previous topic :: View next topic |
| Author |
Message |
bobtills
General User
Joined: 08 Jun 2006
Posts: 5
|
 Posted: Mon Nov 27, 2006 5:51 am Post subject: Adding months to a specified date |
 |
|
I'm sure this is so simple I will be embarassed but here goes:
My brother breeds, and buys & fattens cattle & has to send them to slaughter before they are 30 months old.
He wants a spreadsheet into which he can input the date of birth of a calf & in the next column the date when it will be 30 months old will be calculated for him.
He can then sort by this column and will see when the next animal is due.
Obviously there are much more data he wants to keep in this spreadsheet but it's this I'm having trouble with.
I can add days to the original date - no problem - but because of the way the calendar works 30 months isn't always the same number of days.
Any help would be gratefully received. |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Mon Nov 27, 2006 6:29 am Post subject: |
|
|
bobtills:
If we assume, for example, that the date of birth of the calf, is in cell A2 . . . then . . . the following formula would work.
| Code: | | =DATE(YEAR(A2); MONTH(A2) + 30; DAY(A2)) |
What the above code does, is, break down the date in cell A2, and, add to it 30 months ( as I understand you desire ). Of course, I would suggest putting the value of 30 in a single cell, so, this can be changed/maintained in one place, in the event you want the number of months to change for all calculations.
I hope this helps, please be sure to let me / us know.
Sliderule |
|
| Back to top |
|
|
bobtills
General User
Joined: 08 Jun 2006
Posts: 5
|
| Posted: Mon Nov 27, 2006 8:52 am Post subject: |
|
|
Yes!
Works a treat!
Thank you very much!
 |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Mon Nov 27, 2006 10:55 am Post subject: |
|
|
Just want to add, as a note, when using this technique for 'adding months' . . . if the 'orginial' date is leap year ( for instance, February 29 2008 ) . . . and . . . adding 12 months . . . the 'result' will be set to March . . . ( for instance, March 1 2009 ).
Just wanted to let you ( and anyone else reading this ) know.
Sliderule |
|
| Back to top |
|
|
exec228
Power User
Joined: 11 Dec 2006
Posts: 68
|
| Posted: Wed Dec 13, 2006 1:13 am Post subject: |
|
|
A1 = "2008-12-29"
A2 = DATE(YEAR(A1);MONTH(D16)+12;DAY(A1))
A2 is 2009-12-29
2009 is not a leap year!
___________________________
A1 = "2008-12-29"
A2 = EDATE(A1;12)
A2 is 2009-02-28
seems to be correct
___________________________
p.s.Excel gives 2009-03-01 with month()+12. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Dec 13, 2006 3:45 pm Post subject: |
|
|
| exec228 wrote: | A1 = "2008-12-29"
A2 = DATE(YEAR(A1);MONTH(D16)+12;DAY(A1))
A2 is 2009-12-29
2009 is not a leap year!
|
You are calculating the month of which value in D16?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
exec228
Power User
Joined: 11 Dec 2006
Posts: 68
|
| Posted: Wed Dec 13, 2006 9:27 pm Post subject: |
|
|
neg. it was date within D16, and formula in D17 referring to D16 on my sketch sheet.
when i posted formula here, i changed it to topic context - A1, and forgot to correct formula completely. |
|
| Back to top |
|
|
exec228
Power User
Joined: 11 Dec 2006
Posts: 68
|
| Posted: Wed Dec 13, 2006 9:59 pm Post subject: |
|
|
no. you are right. that is my sheet error. i reproduced it, month(d16) is 12.
i cancel my post. |
|
| Back to top |
|
|
|
