Re: =DATEDIF(3/31/2006,4/30/2006,"m") equals 0?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <marjohn@xxxxxxxxxxx> wrote in message
news:e%23%23VJ9p6GHA.2288@xxxxxxxxxxxxxxxxxxxxxxx
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" <roger@xxxxxxxxxxxxxxxxxxxxxxxx> 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" <DaveF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3D9E2201-2908-403D-ABBD-D72DF84C9667@xxxxxxxxxxxxxxxx
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.





.

Relevant Pages

  • Re: =DATEDIF(3/31/2006,4/30/2006,"m") equals 0?
    ... Roger Govier ... I did not focus on the incorrect date format Steve posted, ... Datedif seems to give an incorrect return of number of months in each ... Biff's suggestion - ...
    (microsoft.public.excel.misc)
  • Re: Color a single digit in a mult-digit number cell
    ... Microsoft MVP - Excel ... A suggestion is to copy the ... > not type anything but choose something under Format (for example Font ... For example, if I type 346 in a cell, I would ...
    (microsoft.public.excel.misc)
  • Re: I dont get how the computer arrives at 2^31
    ... > Why initialize these, when the initial values will never be used? ... The initial value of 'i' isn't used, but the initial value of 'sum' ... fact that it gives the incorrect impression that the initializing value ... Just change the format to "%f\n" to see the difference. ...
    (comp.lang.c)
  • Re: I want to manage my own files (mini-rant)
    ... file in a standard Mac place and in a standard Mac format to *POINT TO* ... But I made no such suggestion. ... Wow - you are calling me a liar in the third post. ... I don't like being coming out with blatent lies as you have done in this ...
    (uk.comp.sys.mac)
  • RE: Formatting negative percent
    ... I'm not sure why you are raining on this idea. ... This suggestion is consistent with what excel already does with other ... Why not a date format of mm/dd/yyyy as a default choice instead of doing ... This post is a suggestion for Microsoft, ...
    (microsoft.public.excel.worksheet.functions)