OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Adding months to a specified date

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
bobtills
General User
General User


Joined: 08 Jun 2006
Posts: 5

PostPosted: Mon Nov 27, 2006 5:51 am    Post subject: Adding months to a specified date Reply with quote

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
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Nov 27, 2006 6:29 am    Post subject: Reply with quote

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
View user's profile Send private message
bobtills
General User
General User


Joined: 08 Jun 2006
Posts: 5

PostPosted: Mon Nov 27, 2006 8:52 am    Post subject: Reply with quote

Yes!
Works a treat!
Thank you very much!

Very Happy
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Nov 27, 2006 10:55 am    Post subject: Reply with quote

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
View user's profile Send private message
exec228
Power User
Power User


Joined: 11 Dec 2006
Posts: 68

PostPosted: Wed Dec 13, 2006 1:13 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Dec 13, 2006 3:45 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
exec228
Power User
Power User


Joined: 11 Dec 2006
Posts: 68

PostPosted: Wed Dec 13, 2006 9:27 pm    Post subject: Reply with quote

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
View user's profile Send private message
exec228
Power User
Power User


Joined: 11 Dec 2006
Posts: 68

PostPosted: Wed Dec 13, 2006 9:59 pm    Post subject: Reply with quote

no. you are right. that is my sheet error. i reproduced it, month(d16) is 12.
i cancel my post.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group