How do I move a date exactly one month forward

[Regouin]

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]

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]

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]

PS: MY previous can also be simplified to:

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

[Regouin]

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]

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]

Sometimes the solutions to your problems are so easy you tend to overlook them. Works like it should Tony. Thanks a lot


frank