Rick,

When you subtract the two dates, it doesn't give you a date/time difference.

Rather, it gives you another date/time. The result of 1:02:07:15 doesn't

mean 1 month 2 days, 7 hours, and 15 minutes. It actually means January 02

(of year 1900) 7:15 AM. Just add in the "yyyy" in your format and you'll see

the year.

If you need a more convincing example, try changing your 2nd date to

4/1/2006 and 4/2/2006. You'll see that your results are 2:29 and 3:01

respectively. It doesn't mean that your answers are 2 months 29 days and 3

months 1 day. It means 2/29/1900 (leap year) and 3/1/1900 respectively.

One solution is to use the DATEDIF function, see

http://www.cpearson.com/excel/datedif.htm for examples of how it's used.

Another way is to format your original cell to [h]:mm to get answer in total

hours and minutes, which would yield 55:15 (55 hours and 15 minutes). Then

from there use more formulas to calculate that in terms of months, days.

I think you'll get a slight difference in number of days if you compare the

answer for the two ways. You'll have to decide which one is more suitable

for you. But at least the difference is not off by a whole month. Good luck.

-Simon

Rick Taylor said:

I am trying to get the total time that it took from the date and time

something was inducted until the date and time it was completed. I have seen

a few suggestions that come close, but not exactly correct.

I.E. cell A1 = 01/30/2006 23:45

cell B1 = 02/02/2006 07:00

cell C1 input the formula =B1-A1 and change the format to M

D:HH:MM

But this usually comes back with a figure like 1:02:07:15, which to me is

wrong as it is stating 1 month, 2 days, 7 hours and 15 minutes. Everything

is okay except for the month, which should be zero, as it really only took 2

days, 7 hours and 15 minutes.

If I do the same but change the second data to 05/22/2006 07:00 then I get a

figure of 4:20:07:15. This again appears wrong to me. It should only be 3

months (FEB, MAR, APR), 21 days, 7 hours 15 minutes.

Any advice is welcomed.