PDA

View Full Version : [SOLVED] How do I move a date exactly one month forward



Regouin
02-25-2005, 05:40 AM
Ok here is what i am looking at.
I am writing a maintenance protocol, with comboboxes I can select certain maintenance jobs and when i selected the job i can tick it completed. When i do i want the old date (last maintenance) replaced by the new one. Then I want excel to start warning me that maintenance is due 1 week ahead of the new maintenance date (which is exactly 1 month after the last maintenance).
Now i have the whole comboboxes stuff worked out but a simple formula that makes 25-02-2005 into 25-03-2005 I cannot accomplish. I know i can do it with a mid command and make it plus 1, this works only till it hits december and has to go to january again and be one year later. Anyone got any ideas how i can work this out or should i just use 30 days instead of one month (25-02-2005+30 gives the date 30 days later), only problem is a gap of 5 or 6 days a year.
please give me some help I am stuck here.

frank

Jacob Hilderbrand
02-25-2005, 06:07 AM
If your date is in A1 try this formula.


=IF(MONTH(A1)=12,DATE(YEAR(A1)+1,1,DAY(A1)), DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))

johnske
02-25-2005, 06:11 AM
Hi,

You could also try this:


Sub testing()
Dim FirstDate As Date, IntervalType$, Number%
IntervalType = "m" ' "m" specifies months as interval.
FirstDate = Now()
MsgBox DateAdd(IntervalType, 1, FirstDate)
End Sub


HTH

johnske
02-25-2005, 06:20 AM
PS: MY previous can also be simplified to:

Sub testing()
MsgBox DateAdd("m", 1, Date)
End Sub

Regouin
02-25-2005, 07:01 AM
Thanks Jake,

that worked fine for me, i didnt want it done using vba, with this formula it worked out fine. You're the best ;)

thanks
frank

TonyJollans
02-25-2005, 07:09 AM
There is no need for the complication of the IF in Jake's formula - the DATE function should cope with year overflows all by itself. Try, simply,


=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Regouin
02-25-2005, 07:43 AM
Sometimes the solutions to your problems are so easy you tend to overlook them. Works like it should Tony. Thanks a lot


frank