Date changes - Im stuck
Posted by Tim on February 12, 2001 1:01 AM
I sure hope someone can help me with this.
I have a user enter the date in A2.
I need blocks A3 to A17 to add a month in each block while keeping the same day.
Heres the problem:
When I try different formulas, if the date is on the 31st of January in A3 then A4 shows a date of March 3 of the same year instead of automatically going to the last day of February. Am I just stupid or missing something? I can't find a macro to do this or any function either.
Help is greatly appreciated!
Tim
Posted by Tim on February 12, 2001 1:04 AM
Also-dates ending on the 31st cause the next month to roll to the 1st-such as a date in A3 of May 31 causes a date of July 1st instead of the 30th of June.-thanks
Posted by Celia on February 12, 2001 1:28 AM
Tim
Assuming your date is in A1 and the number of months to be added is in A2 :-
=IF(DAY(EOMONTH(A1,0))=DAY(A1),EOMONTH(EDATE(A1,A2),0),EDATE(A1,A2))
Please note that using this formula to add 1 month (say) to dates of 28,29,30,31 Jan 2001(for example) will all produce a result of 28 Feb 2001.
It could be argued that the difference between 28 Jan and 28 Feb is not 1 month but 1 month and 3 days. Because of the variable days per month, anomalies such as this are unavoidable when calculating date differences.
Celia
Posted by Aladin Akyurek on February 12, 2001 1:34 AM
Try the function EDATE (Look at EDATE under Index (of Contents and Index). It describes how to activate EDATE if not available on your system).
If A2 contains 1-Jan-2001 and you type in A3
=EDATE(A1,1)
you'll get 1-feb-01
If A2 contains 31-Jan-01, the formula will give you 28-Feb-01.
Hope this helps.
Aladin
Posted by Celia on February 12, 2001 1:35 AM
Follow-up
Sorry, I didn't adjust the formula to fit your specific needs.
Put the following in cell A3 and fill down to A17 :-
=IF(DAY(EOMONTH($A$2,0))=DAY($A$2),EOMONTH(EDATE($A$2,ROW()-2),0),EDATE($A$2,ROW()-2))
Celia
Posted by Dave Hawley on February 12, 2001 2:59 AM
EDATE is the one. Just be aware it is part of the Analysis toolpak found under Tools>Add-ins.
Dave
OzGrid Business Applications
Posted by Celia on February 12, 2001 1:53 PM
Yes, except that it doesn't produce the required result if the start date is the last day of a month with less than 31 days. For example, a start date of 28-2-01 will give 28-3-01.
The following works for all month-end dates :-
=IF(DAY(EOMONTH(A1,0))=DAY(A1),EOMONTH(EDATE(A1,1),0),EDATE(A1,1))
Celia
Posted by Aladin Akyurek on February 12, 2001 2:58 PM
What a glitch! I was merely suggesting the function that is needed.
By the way, would a simpler formula not solve the issue?
=EDATE(A1,1)+IF(DAY(A1)=30,1,IF(DAY(A1)=28,3,0))
Aladin
Posted by Celia on February 12, 2001 3:15 PM
Aladin
I don't think your shorter formula will do it since it depends how many months you want to add. For example, if the start date is 28-Feb and you add 2 months, your formula will give a result of 1-May.
Celia
Posted by Dave Hawley on February 12, 2001 5:56 PM
Hmmm, how about:
What about =IF(A1=EOMONTH(A1,0),EOMONTH(A1,1),EDATE(A1,1))
OzGrid Business Applications
Posted by Celia on February 12, 2001 11:24 PM
Re: Hmmm, how about:
Yes, that's a bit neater.
Celia
