How many months between two date range

[Talladega]

Hi,

I need a formula that can count how many months between two date range.

11/1/08 6/30/09 = 8
6/5/07 8/25/09 = 27
4/3/08 7/7/09 = 15

Thanks,

 

[Bernard Liengme]

Use the undocumented DATEDIF function. Look at Chip's detailed explanation
http://www.cpearson.com/Excel/datedif.aspx
best wishes

 

[Talladega]

I used the formula from the link and it didn't come out right. I have excel
2003.

A13 = 1/6/2006
=DATEDIF(6/30/2009,A13,"m")

It came out to be 1272. It should be 42 months.

 

[Mike H]

Hi,

I think it should be 41, try this

=DATEDIF(A13,DATE(2009,6,30),"m")

Mike

 

[David Biddulph]

On the contrary, it came out right.

You started counting from a time tag of 6 divided by 30 then divided by
2009. That is a tiny fraction of a day from Excel's time origin of 1 Jan
1900. From that date until 6 Jan 2006 is 1272 months (106 years).

If you are trying to count *FROM* 6 Jan 2006 *TO* 30 Sep 2009, then try
=DATEDIF(A13,DATE(2009,9,30),"m")
That gives 44 months. Perhaps you can explain how you get 42 months?

 

[Talladega]

Let me see, i just counted with my fingers.

1/6/2006 thru 12/2006 is 12 months
1/2007 thru 12/2007 is 12 months
1/2008 thru 12/2008 is 12 months
1/2009 thru 6/30/09 is 6 months

all the months add up to 42.

 

[Mike H]

Sarcasm is the lowest form of wit particularly when directed at someone
trying to help!!

Get your fingers out again and try this

The numbers 1 to 10 (that's all your fingers I don't want to extend you, no
toes)

1 to 10 =10 do we agree?
similar to your 1/6/2006 thru 12/2006 is 12 months

the diference between 1 and 10?

Quiet at the back please

let me think!! OK 10-1 =9

please sir the diference is 9 and that's how Datedif works

Mike

 

[Talladega]

Thanks for the clarification and the helpful formula!

I didn't mean to startle anyone.

 

[Mike H]

Your welcome and if you want to include the start month then simply put +1 on
the end of the datedif formula.

Mike

 

[JoeU2004]

Let me see, i just counted with my fingers.
1/6/2006 thru 12/2006 is 12 months

No, it isn't. Consider this: how many months are there from 1/6/2006 to
2/6/2006? The correct answer is: one. So it is 11 months from 1/6/2006 to
12/6/2006.

1/2007 thru 12/2007 is 12 months

Well, you skipped a month, namely: 12/6/2006 to 1/6/2007. So the next
period is 12/6/2006 to 12/6/2007, which is indeed 12 months.

1/2008 thru 12/2008 is 12 months
1/2009 thru 6/30/09 is 6 months

Again, you are skipping months. Those time periods should be 12/6/2007 to
12/6/2008 (12 months) and 12/6/2008 to 6/30/2009 (6 months and 24 days).

So the total is 11+12+12+6 = 41 months and 24 days.

all the months add up to 42.

That depends on how you want to treat additional time (24 days) beyond an
integral number of months. That's really up to you. There is no right or
wrong way; it all depends on your application (purpose). But you do need to
know what the tools (functions) do, and you need to make adjustments if
their "judgment" differs from you. Most functions are not mindreaders ;-).

DATEDIF truncates. If you want to round up, you might use some heuristic
like the following. Suppose 1/6/2006 is in A1 and 6/30/2006 is in B1.
Then:

=datedif(A1,B1) + (edate(A1,datedif(A1,B1))<>B1)

If you get a #NAME? error, see the Help page for the EDATE() function.

HTH.


----- original posting -----

 

[JoeU2004]

Your welcome and if you want to include the start month
then simply put +1 on the end of the datedif formula.

I don't think it is that simple. Two cases to consider:


1. =datedif(datevalue("1/10/2008"),datevalue("2/10/2008"),"m")

Should always result in 1. If we "simply put +1 on the end", we get 2.


2. =datedif(datevalue("2/28/2007"),datevalue("2/29/2008"),"m")

In some contexts, we might reasonably want the result to be 12. If we "simply put +1 on the end", we get 13.

FYI, the same can be said for my proposal of adding 1 depending on EDATE().


How about:

=roundup(days360(A1,B1)/30,0)


----- original posting -----

 

[HARSHAWARDHAN. S .SHASTRI]

Try this

=INT((A2-A1)/30)

Where A1 and A2 are end and start date.