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

Hi


=DATEDIF(StartDate,EndDate,"M")


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"nginhong" wrote in message
...
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

Hello Arvi,

It seems like the formula you provided contains error.

BR//nginhong

"Arvi Laanemets" wrote:

Hi


=DATEDIF(StartDate,EndDate,"M")


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"nginhong" wrote in message
...
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

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.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"nginhong" wrote in message
...
Hello Arvi,

It seems like the formula you provided contains error.

BR//nginhong

"Arvi Laanemets" wrote:

Hi


=DATEDIF(StartDate,EndDate,"M")


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"nginhong" wrote in message
...
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

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" wrote:

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.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"nginhong" wrote in message
...
Hello Arvi,

It seems like the formula you provided contains error.

BR//nginhong

"Arvi Laanemets" wrote:

Hi


=DATEDIF(StartDate,EndDate,"M")


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"nginhong" wrote in message
...
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

Hi


"nginhong" wrote in message
...
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")


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

On Wed, 12 Apr 2006 03:21:01 -0700, nginhong
wrote:

=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

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

Closer?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532164

Hello John,

It seems like the formula you provided contains error.

BR//nginhong

"John James" wrote:


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

Closer?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532164

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 a-
=(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" wrote:


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

Closer?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532164

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)


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532164

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)

nginhong Wrote:

=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)



--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532164

Thanks John,

It works fine now!

BR//nginhong

"John James" wrote:


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)

nginhong Wrote:

=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)



--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532164