Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get total hours from 2 date/time cells?
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:DD: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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get total hours from 2 date/time cells?
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" wrote: 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:DD: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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get total hours from 2 date/time cells?
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 "Rick Taylor" wrote in message ... 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:DD: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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get total hours from 2 date/time cells?
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 "SiC" wrote: 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" wrote: 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:DD: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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get total hours from 2 date/time cells?
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. "Fred Smith" wrote: 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 "Rick Taylor" wrote in message ... 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:DD: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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting a total for a row of cells where some are text | Excel Discussion (Misc queries) | |||
Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins? | Excel Discussion (Misc queries) | |||
Sum minutes and seconds to total hours | Excel Discussion (Misc queries) | |||
Sumproduct help needed! | Excel Worksheet Functions | |||
ADDING SUM TOTAL OF MORE THAN 30 CELLS IN A COLUMN TOGETHER - WON. | Excel Discussion (Misc queries) |