Excel problems

[minip90hotmailcom]

Hey there I have a problem I can't fix. The formal A1 date 10/10 A2 10/10 A3 =sum(a1-a2) and I want the results in mm:DD it comes back with 01:00 with should be 00:00 and if I change the dates to let's say 10/10 - 10/11 it results in 02:00 with should be 01:00 ? What is the problem?

[#2]

@minip90hotmailcom 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@minip90hotmailcom 

It might be useful to show a copy of the range A1:A3 using 'general' format.

[#4]

@minip90hotmailcom 

Normally, on a PC (not a Mac)....

For the first example, subtracting the same dates does result in zero.  But as a date, that is 0 Jan 1900.

So the format mm:dd displays 01:00 because mm is the month number (1), and dd is day number (0), not a number of months and number of days.

There is no format for the latter.

In fact, when we subtract dates, the result is an integer number of days.  So in general, we cannot convert that to a number of months and number of days because the number of days per month varies from 28 to 31.

dd displays 0 to 31 only by coincidence.  For example, if we enter the number 32 and format as mm:dd, we see 02:01 because as a date, 32 is 1 Feb 1900.  Again, mm is the month number (2), and dd is the day number (1).

BTW, you can enter simply =A1-A2.  The use of SUM in that context is superfluous.

Although there is no format to display number of months and number of days, we might use DATEDIF in a formula, to wit:

=TEXT(DATEDIF(A1,A2,"m"),"00:") & TEXT(DATEDIF(A1,A2,"md"),"00")

That displays the string 00:00.

-----

I think you misstated the facts of the second example.

I assume that your date form is DMY.  So 10/10 is 10 Oct, and 10/11 is 10 Nov.

Regardless, 10/10 - 10/11 results in a negative number.  Normally, Excel does not display negative time.  Instead, it displays #### regardless of the column width.  And we hover the cursor over the cell, we see a format error.

(Unless you are using a Mac or a PC with the 1904 date system options enabled.  But that is not consistent with your facts that you posted.)

But if you meant 10/11 - 10/10, the difference is 31 days.  If we format that as mm:dd, we see 01:31 because, again, mm is the month number (1), and dd is the day number (31).

Instead, again, the following formula might work for you:

=TEXT(DATEDIF(A1,A2,"m"),"00:") & TEXT(DATEDIF(A1,A2,"md"),"00")

When A1 is 10/10 (10 Oct) and A2 is 10/11 (10 Nov), that displays the string 01:00.

Caveat:  There is no universal way to count months (start to end vs end to start), especially when one date is 28 or 29 Feb.  You might not agree with DATEDIF's interpretation.  Moreover, "md" has not been reliable in the past.