MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formula calcuating number of months


Posted by DENIS on September 23, 2001 3:49 AM

Does anyone know a formula to calculate the number of month's between to dates ?

Thanks for any help


Posted by Aladin Akyurek on September 23, 2001 4:31 AM

Try:

=DATEDIF(A1,B1,"M")

where A1 and B1 houses dates with B1 > A1 (i.e., B1 is later in time than A1).

Posted by denis on September 23, 2001 6:30 AM

I have a spreadsheet with a backround pic, i want to reduce the size of the file from 3.5 mb to about 1mb. I only use about 15 columns & if i get rid of the other column's it will also get rid of the backround's in those column's, thus reducing the size of the file

Posted by denis on September 23, 2001 6:36 AM

Thanks Aladin

Much a appreciated

Posted by IML on September 24, 2001 6:41 AM

datedit


Aladin,
This function works for me, but doesn't appear in the function drop down or help file. (analysis tool pak is activiated). I assume there are three arguments
1) begin date
2) end date
3) d, m, or y only as qualifiers

Does that sound about right?

Thanks,
Ian

Posted by Aladin Akyurek on September 24, 2001 9:32 AM

Re: datedit

Yep. The 3rd arg indicates the time unit of interest. It can even be: "MD", "YM", or "YD".

Aladin

Posted by IML on September 24, 2001 10:03 AM

Re: datedif


As always, thanks for the information. I'm a little confused on the multiple qualifiers. For example, with
using 1/1/00 and 9/24/01, I get some strange results:
=DATEDIF(36526,37158,"md") the result is 23
=DATEDIF(36526,37158,"ym") is 8. What are these?
Forgive me if I am missing the obvious.

Thanks again,
Ian

Posted by Aladin Akyurek on September 24, 2001 10:28 AM

Re: datedif

using 1/1/00 and 9/24/01, I get some strange results: =DATEDIF(36526,37158,"md") the result is 23 =DATEDIF(36526,37158,"ym") is 8. What are these? Forgive me if I am missing the obvious.

Ian --

"md" invokes computing the difference between the days in begin date and end date, where the months and years of the dates of interest are ignored. In your example, it's 24-1=23.

"ym" invokes computing the difference between the months in begin date and end date, where the days and years of the dates of interest are ignored. In your example, it's 9-1=8.

"yd" invokes computing the difference between the days in begin date and end date, where the years of the dates of interest are ignored. In your example, it's 267 = Sep 24 - Jan 1 (Excel looks though at the year of the begin date behind the scenes.)

Aladin


Posted by IML on September 24, 2001 11:14 AM

Re: datedif

That makes sense logically. I'm sure there is a use for the "md"s etc, I just can't think of one now.
Thanks again for the clarification.
Ian