How to calculate "number of months" between two given date?

[Guest]

Example 1
Start date: 12/04/2004
End date: 12/04/2006
The formula should give the answer to 24 months

Example 2
Start date: 12/04/2004
End date: 13/04/2006
The formula should give the answer to 25 months

When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not
show 25 months for "Example 2" as it is still within the same month "April"

Your kind support is greatly appreciated.

TQ!//nginhong

 

[Arvi Laanemets]

Hi


=DATEDIF(StartDate,EndDate,"M")

 

[John James]

How about
=(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2
+ROUNDUP((DAY(A3)-DAY(A2))/31,0

Closer

 

[Guest]

Hello John,

It seems like the formula you provided contains error.

BR//nginhong

 

[Guest]

Hello Arvi,

It seems like the formula you provided contains error.

BR//nginhong

 

[Arvi Laanemets]

Hi

What kind of error?

I'm afraid you dates aren't really dates at all, but strings. Change the
format for some date to general - when the value in cell turns to number,
then the entry was a date, otherwise it was not.

 

[Guest]

Hello Arvi,

Thanks for you reply.

Let me know frame the idea of how the excel sheet looks like.

I put start date in cell A2 = 12/04/2004 & end date A3 = 13/04/2006
Then use your formula as in cell A4 =DATEDIF((A2),(A3),"M") and press enter,
however error message shows:-

The formula you typed contains error.
- For information about fixing common formula problems, click Help.
- To get assistance in entering a function, click OK, then click Function on
the Insert menu.
- If you are not trying to enter a formula, avoid using an equal sign (=) or
minus sign (-), or precede it with a single quotation mark (')

BR//nginhong

 

[Arvi Laanemets]

Hi

Hello Arvi,

Thanks for you reply.

Let me know frame the idea of how the excel sheet looks like.

I put start date in cell A2 = 12/04/2004 & end date A3 = 13/04/2006

Select cells A2:A3, and set cell format to General. When those are really
dates, then now you see values 38089 and 38819

Then use your formula as in cell A4 =DATEDIF((A2),(A3),"M") and press
enter,

You can simplify the formula a bit
=DATEDIF(A2,A3,"M")

however error message shows:-

The formula you typed contains error.
- For information about fixing common formula problems, click Help.
- To get assistance in entering a function, click OK, then click Function
on
the Insert menu.
- If you are not trying to enter a formula, avoid using an equal sign (=)
or
minus sign (-), or precede it with a single quotation mark (')

What is function parameter delimiter for your regional settings? Comma or
semicolon? Try:
=DATEDIF(A2;A3;"M")

 

[Ron Rosenfeld]

=DATEDIF((A2),(A3),"M")

Perhaps your country version of excel uses semicolons for delimiters?

=DATEDIF((A2);(A3);"M")

Also, the parentheses around the cell references are unnecessary:

=DATEDIF(A2;A3;"M")


--ron

 

[Guest]

dHello Ron,

You are right! The country setting is using semicolons for delimiters and
managed to use the formula but the result is not what I wanted.

Thanks & Regards,
nginhong

 

[Guest]

Hello John,

I managed to find out the error of the formula because the regional setting
in my PC is using semicolon for delimiters.

I would say you formula is very close to what I am looking forward but the
set back are:-
=(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2)+ROUNDUP((DAY(A3)-DAY(A2))/31;0)

1. Unable to show result as 1 month if start date is 28/02/2006 and end date
is 02/03/2006.
2. Unable to show result as 13 months if start date is 28/02/2006 and end
date is 02/03/2007.

However I really appreciate your help.

Thanks & Regards,
Ngin Hong

 

[John James]

Hi Ngin Hong,

So does this work then?

=(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2)+ROUNDUP(IF(DAY(A3)>DAY(A2),DAY(A3)-DAY(A2),0)/31;0)

 

[Ron Rosenfeld]

dHello Ron,

You are right! The country setting is using semicolons for delimiters and
managed to use the formula but the result is not what I wanted.

Thanks & Regards,
nginhong

Is it that if the time frame is 24 months plus one day you want to show 25
months as a result?


--ron

 

[Carim]

Hi,

=DATEDIF(A2,B2,"m")+(DAY(A2)>DAY(B2))+1

HTH
Cheers
Carim

 

[Ron Rosenfeld]

Hi,

=DATEDIF(A2,B2,"m")+(DAY(A2)>DAY(B2))+1

HTH
Cheers
Carim

Start date 28-Feb-2006
End date 2-Mar-2007


Your formula --> 14

OP wants --> 13

Start date 28-Feb-2006
End date 2-Mar-2006

Your formula -->2
OP wants --> 1



--ron

 

[John James]

Hi Ngin Hong,

Simplifying it:
Just adding this to your original formula should work I think
+IF(DAY(A4)>DAY(A3),1,0)

gives:
=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)+IF(DAY(A4)>DAY(A3),1,0)

 

[Guest]

Thanks John,

It works fine now!

BR//nginhong

Hi Ngin Hong,

Simplifying it:
Just adding this to your original formula should work I think
+IF(DAY(A4)>DAY(A3),1,0)

gives:
=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)+IF(DAY(A4)>DAY(A3),1,0)

 

[Carim]

Hi,

=DATEDIF(A2,B2,"m")+(DAY(A2)>DAY(B2))

Would that be what you are looking for ?

Carim

 

[Guest]

Hello Carim,

Thanks for help!
I think the following formula suit my request:-
=(YEAR(B3)-YEAR(A3))*12+MONTH(B3)-MONTH(A3)+IF(DAY(B3)>DAY(A3),1,0)

Because I need to show "1 month" even it is 1 day after the start date.
e.g. start date: 27/02/2006, end date: 28/02/2006, it must show "1 month"
not "0 month"

Thanks & regards,
nginhong

 

[Ron Rosenfeld]

Hello Carim,

Thanks for help!
I think the following formula suit my request:-
=(YEAR(B3)-YEAR(A3))*12+MONTH(B3)-MONTH(A3)+IF(DAY(B3)>DAY(A3),1,0)

Because I need to show "1 month" even it is 1 day after the start date.
e.g. start date: 27/02/2006, end date: 28/02/2006, it must show "1 month"
not "0 month"

Thanks & regards,
nginhong

What do you want for a result with:

1/30/2006
2/28/2006


--ron