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

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 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

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

"Ron Rosenfeld" wrote:

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

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

On Wed, 12 Apr 2006 05:16:02 -0700, nginhong
wrote:

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

Hi,

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

HTH
Cheers
Carim


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

On Thu, 13 Apr 2006 16:23:24 -0500, Carim
wrote:


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

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

Hi,

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

Would that be what you are looking for ?

Carim


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

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


"Carim" wrote:


Hi,

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

Would that be what you are looking for ?

Carim


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

On Sun, 16 Apr 2006 01:49:01 -0700, nginhong
wrote:

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

Hi nginhong

if this formula works for you...

=(YEAR(B3)-YEAR(A3))*12+MONTH(B3)-MONTH(A3)+IF(DAY(B3)DAY(A3),1,0)

then this should too

=DATEDIF(A3,B3,"m")+(DATEDIF(A3,B3,"md")0)


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

Allowing for negative date differences on cells B3 and A3:

moi:
=(YEAR(B3)-YEAR(A3))*12+MONTH(B3)-MONTH(A3)+IF(B3A3,IF(DAY(B3)DAY(A3),1,IF(B3<A3,-1,0)))

daddylonglegs:
=IF(B3A3,DATEDIF(A3,B3,"m")+(DATEDIF(A3,B3,"md") 0),-(DATEDIF(B3,A3,"m")+(DATEDIF(B3,A3,"md")0)))

How do you find the parameters for the datedif formula, given that
there's no help in excel or on the web site?


--
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

Excel 2000 had a help file but since the function returns erroneous data
under certain circumstances I guess MS just let it go

http://www.cpearson.com/excel/datedif.htm

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"John James" wrote
in message ...

Allowing for negative date differences on cells B3 and A3:

moi:
=(YEAR(B3)-YEAR(A3))*12+MONTH(B3)-MONTH(A3)+IF(B3A3,IF(DAY(B3)DAY(A3),1,IF(B3<A3,-1,0)))

daddylonglegs:
=IF(B3A3,DATEDIF(A3,B3,"m")+(DATEDIF(A3,B3,"md") 0),-(DATEDIF(B3,A3,"m")+(DATEDIF(B3,A3,"md")0)))

How do you find the parameters for the datedif formula, given that
there's no help in excel or on the web site?


--
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 again Peo,
#24

Peo Sjoblom Wrote:
Excel 2000 had a help file but since the function returns erroneous
data
under certain circumstances I guess MS just let it go

http://www.cpearson.com/excel/datedif.htm




--
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