G'day all,

I have an interest in solving a DATEDIF problem similar to Daves, so have
been watching this thread with interest.

After trying the various suggestions I'm rather confused. I got the
following results (remembering that we use the date format dd/mm/yyyy):

Tom's suggestion -

=DATEDIF(DATEVALUE("31/03/2006"),DATEVALUE("30/04/2006"),"m") = 0

=DATEDIF(DATEVALUE("31/03/2006"),DATEVALUE("31/05/2006"),"m") = 2

Biff's suggestion -

=DATEDIF("31/03/2006","30/04/2006","m") = 0

=DATEDIF("31/03/2006","31/05/2006","m") = 2

Roger's suggestion -

With 31/03/2006 in A1 and 30/04/2006 in B1 -

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1)) = 1

With 31/03/2006 in A1 and 31/05/2006 in B1 -

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1)) = 2

To my way of thinking, Roger's solution is the only one that returns the
correct result in both situations.

Any comments, suggestions, etc.?

Regards,

John

"Roger Govier" wrote in message news:...
Hi Dave

Slightly easier when dates are in cells
=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1))

--
Regards

Roger Govier


"Dave F" wrote in message
...
How does that work?

=DATEDIF(3/31/2006,5/31/2006,"m") = 2

How can I get Excel to count exactly one month between the dates
3/31/2006
and 4/30/2006, or between 4/30/2006 and 5/31/2006, etc.?

Dave


--
Brevity is the soul of wit.

Hi John

To be honest, I hadn't realised that Datedif 31/03/06 to 30/04/06 gave a
problem with a zero result until Steve posted his problem.
I did not focus on the incorrect date format Steve posted, as I had seen
him posting in a different thread using Datedif for depreciation
calculation, so realised he knew the correct format.

However, I was able to replicate his problem, and posted a potential
solution which adds 1 to the result, where the last day of the later
month is less than the last day of the previous month.
Datedif seems to give an incorrect return of number of months in each
case where this happens

--
Regards

Roger Govier


"John Taylor" wrote in message
...
G'day all,

I have an interest in solving a DATEDIF problem similar to Daves, so
have been watching this thread with interest.

After trying the various suggestions I'm rather confused. I got the
following results (remembering that we use the date format
dd/mm/yyyy):

Tom's suggestion -

=DATEDIF(DATEVALUE("31/03/2006"),DATEVALUE("30/04/2006"),"m") = 0

=DATEDIF(DATEVALUE("31/03/2006"),DATEVALUE("31/05/2006"),"m") = 2

Biff's suggestion -

=DATEDIF("31/03/2006","30/04/2006","m") = 0

=DATEDIF("31/03/2006","31/05/2006","m") = 2

Roger's suggestion -

With 31/03/2006 in A1 and 30/04/2006 in B1 -

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1)) = 1

With 31/03/2006 in A1 and 31/05/2006 in B1 -

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1)) = 2

To my way of thinking, Roger's solution is the only one that returns
the correct result in both situations.

Any comments, suggestions, etc.?

Regards,

John

"Roger Govier" wrote in message
news:...
Hi Dave

Slightly easier when dates are in cells
=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1))

--
Regards

Roger Govier


"Dave F" wrote in message
...
How does that work?

=DATEDIF(3/31/2006,5/31/2006,"m") = 2

How can I get Excel to count exactly one month between the dates
3/31/2006
and 4/30/2006, or between 4/30/2006 and 5/31/2006, etc.?

Dave


--
Brevity is the soul of wit.

Hi
until Steve posted his problem

that should have read
until DaveF posted his problem.

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi John

To be honest, I hadn't realised that Datedif 31/03/06 to 30/04/06 gave
a problem with a zero result until Steve posted his problem.
I did not focus on the incorrect date format Steve posted, as I had
seen him posting in a different thread using Datedif for depreciation
calculation, so realised he knew the correct format.

However, I was able to replicate his problem, and posted a potential
solution which adds 1 to the result, where the last day of the later
month is less than the last day of the previous month.
Datedif seems to give an incorrect return of number of months in each
case where this happens

--
Regards

Roger Govier


"John Taylor" wrote in message
...
G'day all,

I have an interest in solving a DATEDIF problem similar to Daves, so
have been watching this thread with interest.

After trying the various suggestions I'm rather confused. I got the
following results (remembering that we use the date format
dd/mm/yyyy):

Tom's suggestion -

=DATEDIF(DATEVALUE("31/03/2006"),DATEVALUE("30/04/2006"),"m") = 0

=DATEDIF(DATEVALUE("31/03/2006"),DATEVALUE("31/05/2006"),"m") = 2

Biff's suggestion -

=DATEDIF("31/03/2006","30/04/2006","m") = 0

=DATEDIF("31/03/2006","31/05/2006","m") = 2

Roger's suggestion -

With 31/03/2006 in A1 and 30/04/2006 in B1 -

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1)) = 1

With 31/03/2006 in A1 and 31/05/2006 in B1 -

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1)) = 2

To my way of thinking, Roger's solution is the only one that returns
the correct result in both situations.

Any comments, suggestions, etc.?

Regards,

John

"Roger Govier" wrote in message
news:...
Hi Dave

Slightly easier when dates are in cells
=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1))

--
Regards

Roger Govier


"Dave F" wrote in message
...
How does that work?

=DATEDIF(3/31/2006,5/31/2006,"m") = 2

How can I get Excel to count exactly one month between the dates
3/31/2006
and 4/30/2006, or between 4/30/2006 and 5/31/2006, etc.?

Dave


--
Brevity is the soul of wit.