[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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.
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
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
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!
Sliderule
Super User

Joined: 29 May 2004
Posts: 2499
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
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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
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 https://forum.openoffice.org
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.
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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum