Calculating # of Months B/T Dates

S

S Jackson

I have a spreadsheet with dates in column F and column H. I want to format
a cell to calculate the number of months that have lapsed between these two
columns. I tried this formula:

=DATEDIF(F2,H2,"m")

But it doesn't give me exactly what I want. I want the result to give me
fractions of months. For example:

Column F = 09/28/2000
Column H = 11/06/2000
No. of months b/t = 1.30

Please forgive me if this question has been asked and answered many times
over, but I could not find anything when I performed a search.
TIA
 
N

Niek Otten

It all depends on how you define months. The difference between the two
dates you give is 39 days. Why is that 1.30 months?
Is a month 28,29,30 or 31 days? So is the remainder of one month in your
example 11, 10, 9 or 8 days? What should that be divided into? 28?....etc

Or would you like to use "the" 360-days system (there are several)?

I'm sure we can help you if you specify your requirements
 
R

Ron Rosenfeld

I have a spreadsheet with dates in column F and column H. I want to format
a cell to calculate the number of months that have lapsed between these two
columns. I tried this formula:

=DATEDIF(F2,H2,"m")

But it doesn't give me exactly what I want. I want the result to give me
fractions of months. For example:

Column F = 09/28/2000
Column H = 11/06/2000
No. of months b/t = 1.30

Please forgive me if this question has been asked and answered many times
over, but I could not find anything when I performed a search.
TIA
Click to expand...

I guess you have to define exactly what you are doing.

For example, between those two dates you have one full calendar month (October)
and then, depending how you are counting, either two or three days in September
(depending on whether you are counting inclusively or not) plus six days in
November.

So to me that would be 1 + 2/30 + 6/30 or 1.2667

But you may have different rules for counting, which should be taken into
account in your formula.


--ron
 
S

S Jackson

I am so sorry I didn't give you enough information (I'm trying to get this
information for someone else and I wasn't clear on what they wanted).

The original formula that this person was using to obtain the result 1.30
months was as follows:

=(H3-F3)/30

It seems to me that the forumla she has now seems to work fine. However,
until I can clarify with this individual exactly what they want, do you have
any recommendations?
 
R

Ron Rosenfeld

I am so sorry I didn't give you enough information (I'm trying to get this
information for someone else and I wasn't clear on what they wanted).

The original formula that this person was using to obtain the result 1.30
months was as follows:

=(H3-F3)/30

It seems to me that the forumla she has now seems to work fine. However,
until I can clarify with this individual exactly what they want, do you have
any recommendations?
Click to expand...

If she's happy with the results, that's probably all that matters. But,
because of the differing numbers of days in a month, some convention is
required. So long as the user is aware that not all months have 30 days, and
that some answers may therefore seem a bit unusual, all is well.

Like 1 Feb 2006 to 2 Mar 2006 = 0.97 months.



--ron
 
N

Niek Otten

<some convention is required>

One convention is the 360-days system, implemented in the DAYS360 function.
There are two standards: European and US (NASD).
It is often used for example in actuarial and other financial systems and
makes working with "months" quite a lot easier.
 
Top