Deduct Months from a Date

D

DaveMoore

In Column 'D' I have a list of dates in the form MMMM/YYY
Column 'E' contains whole numbers which represent a no. of months
In column 'F' I wish to deduct the number of months in 'E' from 'D'

Can anyone help?
 
B

Boris

In Column 'D' I have a list of dates in the form MMMM/YYY
Column 'E' contains whole numbers which represent a no. of months
In column 'F' I wish to deduct the number of months in 'E' from 'D'

Can anyone help?
Click to expand...

Use this function:
---
Function MonthAdd(dDate As Variant, nMonth As Variant) As Variant
MonthAdd = DateAdd("m", nMonth, dDate)
End Function
---

In worksheet use =MONTHADD(D5,E5)
This function will add months to your date, and if you want it to subtract,
just call it with negative argument for month: =MONTHADD(D5,-E5)

Regards,
B.
 
R

Rick Rothstein \(MVP - VB\)

In Column 'D' I have a list of dates in the form MMMM/YYY
Column 'E' contains whole numbers which represent a no. of months
In column 'F' I wish to deduct the number of months in 'E' from 'D'
Click to expand...

Do you really have a 3-digit year? If so, is that the format you want your
answer in? Also, again assuming you really have a 3-digit year, what is the
earliest year your date can have (I'm trying to see if you have to have
logic to handle 19xx type years)?

Rick
 
D

DaveMoore

In Column 'D' I have a list of dates in the form MMMM/YYY
Column 'E' contains whole numbers which represent a no. of months
In column 'F' I wish to deduct the number of months in 'E' from 'D'

Can anyone help?
Click to expand...

Thank you both Mike & Boris.
Playing around I found that EOMONTH worked also
Dave Moore
 
D

DaveMoore

Do you really have a 3-digit year? If so, is that the format you want your
answer in? Also, again assuming you really have a 3-digit year, what is the
earliest year your date can have (I'm trying to see if you have to have
logic to handle 19xx type years)?

Rick
Click to expand...

My mistake Rick. The format is in fact MMMM YYYY so today's date
would return September 2007.
Dates in my list in column 'D' are all later than the year 2000
although the result in column 'F. may well be 19xx.
As mentioned in my mail that crossed with yours I used EOMONTH
successfully.
Thanks very much,
Dave Moore
 
B

Balan

Mr.Dave,
I am happy you are satisfied that EOMonth worked as well. But I notice that
the results of EOMONTH and the suggestions given by Mike and Boris would be
different. I am thankful that I have come to know about EOMONTH from your
reply.

I am a novice to formulae and programming. From Boris I would like to get
one clarification . DateAdd is not defined but it is working-- how ? I do
not find any excel worksheet function called Dateadd. Further, what "m"
inside the brackets of dateadd indicate -- months ? Is dateadd a function of
VBA ? I shall be grateful for these clarifications.

Balan
 
Top