How do I get total hours from 2 date/time cells?

[Rick Taylor]

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 MD: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.

 

[SiC]

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

 

[Fred Smith]

When you ask Excel to display months, it displays the month of the year, not the
number of months.

Your formula gives the answer of 2 days, 7 hours and 15 minutes. For this
answer, Excel stores the date/time of 1900 Jan 2 07:15. When you ask it to
display the month, it dutifully displays month number 1 (January).

You can't display the number of months with a date/time format. In your case,
why bother, as the number is zero. If you are simply looking for the total hours
and minutes, use the format [hh]:mm which will display hours over 24.

 

[Rick Taylor]

SiC,

Thank you for your response. The [h]:mm will work for me for now. But what
you mentioned about the number that I was getting back from my formula is not
correct. I input A1: 4/1/2006 7:00; B1: 4/2/2006 8:00; and in cell C1 the
formula m:dd:hh:mm and it returned 1:01:01:00 (i.e. 1 Month, 1 Day, 1 Hour
and no minutes); in A2: 12/18/2005 12:00; B2: 6/5/2006 9:00; and c2 returned
6:16:21:00 (i.e. 6 months, 16 days, 21 hours, zero minutes.

Again I could be interpreting the data wrong. But I do thank you for your
response and the formula [h]:mm

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

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 MD: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.

 

[Rick Taylor]

Please see my reply to SiC. I was trying for the months as sometimes we do
have items in work for many months. Sic mentioned a similar format to the
columns, though I stated it as a formula in the reply to him.

Thanks for your prompt response.

When you ask Excel to display months, it displays the month of the year, not the
number of months.

Your formula gives the answer of 2 days, 7 hours and 15 minutes. For this
answer, Excel stores the date/time of 1900 Jan 2 07:15. When you ask it to
display the month, it dutifully displays month number 1 (January).

You can't display the number of months with a date/time format. In your case,
why bother, as the number is zero. If you are simply looking for the total hours
and minutes, use the format [hh]:mm which will display hours over 24.

--
Regards,
Fred

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 MD: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.