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<at>tarkon.ee )


"nginhong" <(E-Mail Removed)> wrote in message
news3EEC526-8567-4ACD-BBD8-(E-Mail Removed)...
> 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 Jame
-----------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...fo&userid=3269
View this thread: http://www.excelforum.com/showthread.php?threadid=53216

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<at>tarkon.ee )
>
>
> "nginhong" <(E-Mail Removed)> wrote in message
> news3EEC526-8567-4ACD-BBD8-(E-Mail Removed)...
> > 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<at>tarkon.ee )



"nginhong" <(E-Mail Removed)> wrote in message
news:18E780F3-73E6-4F34-97E1-(E-Mail Removed)...
> 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<at>tarkon.ee )
>>
>>
>> "nginhong" <(E-Mail Removed)> wrote in message
>> news3EEC526-8567-4ACD-BBD8-(E-Mail Removed)...
>> > 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<at>tarkon.ee )
>
>
>
> "nginhong" <(E-Mail Removed)> wrote in message
> news:18E780F3-73E6-4F34-97E1-(E-Mail Removed)...
> > 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<at>tarkon.ee )
> >>
> >>
> >> "nginhong" <(E-Mail Removed)> wrote in message
> >> news3EEC526-8567-4ACD-BBD8-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
newsE8347CE-181F-4FCE-A7A3-(E-Mail Removed)...
> 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<at>tarkon.ee )

On Wed, 12 Apr 2006 03:21:01 -0700, nginhong
<(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
>