Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Format for Time Elapsed in Decimal
Excel 2007, in column A I have 5 rows of Time(in)
In column B is Time(out). The data is formatted to show AM, PM In C2, the elapsed time is calculated for row 2, for example =B2-A2, and fomula is filled down to C. This is formatted [h]:mm, yielding an elapsed time (for example 6 hours 45 minutes displayed as 6:45) At the bottom of the data in C I add them up to get the total elapsed time. If the this adds to 38:15 (38 hours 15 minutes), how can I format this to display as 38.25 hours Thank you Marsh |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Format for Time Elapsed in Decimal
Multiply by 24:
A1 = 38:15 =A1*24 Format as General or Number Result = 38.25 -- Biff Microsoft Excel MVP "Marsh" wrote in message ... Excel 2007, in column A I have 5 rows of Time(in) In column B is Time(out). The data is formatted to show AM, PM In C2, the elapsed time is calculated for row 2, for example =B2-A2, and fomula is filled down to C. This is formatted [h]:mm, yielding an elapsed time (for example 6 hours 45 minutes displayed as 6:45) At the bottom of the data in C I add them up to get the total elapsed time. If the this adds to 38:15 (38 hours 15 minutes), how can I format this to display as 38.25 hours Thank you Marsh |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Format for Time Elapsed in Decimal
Say your total is in C10 then in D10:
INT(C10)*24+HOUR(C10)+ROUND(MINUTE(C10)/60,2) and format as General Hope this helps "Marsh" wrote: Excel 2007, in column A I have 5 rows of Time(in) In column B is Time(out). The data is formatted to show AM, PM In C2, the elapsed time is calculated for row 2, for example =B2-A2, and fomula is filled down to C. This is formatted [h]:mm, yielding an elapsed time (for example 6 hours 45 minutes displayed as 6:45) At the bottom of the data in C I add them up to get the total elapsed time. If the this adds to 38:15 (38 hours 15 minutes), how can I format this to display as 38.25 hours Thank you Marsh |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I found it, never mind - Format for Time Elapsed in Decimal
=(b2-a2-int(b2-a2))*24
"Marsh" wrote: Excel 2007, in column A I have 5 rows of Time(in) In column B is Time(out). The data is formatted to show AM, PM In C2, the elapsed time is calculated for row 2, for example =B2-A2, and fomula is filled down to C. This is formatted [h]:mm, yielding an elapsed time (for example 6 hours 45 minutes displayed as 6:45) At the bottom of the data in C I add them up to get the total elapsed time. If the this adds to 38:15 (38 hours 15 minutes), how can I format this to display as 38.25 hours Thank you Marsh |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
I found it, never mind - Format for Time Elapsed in Decimal
"Marsh" wrote:
Subject: I found it, never mind - Format for Time Elapsed in Decimal =(b2-a2-int(b2-a2))*24 That will work, and it is necessary if B2 and/or A2 contains dates (specifically different days) as well as time. But it is unnecessary if B2 and A2 contain just times, as you said they did. In that case, ostensibly: =(B2-A2)*24 is sufficient. However.... At the bottom of the data in C I add them up to get the total elapsed time. If the this adds to 38:15 (38 hours 15 minutes), how can I format this to display as 38.25 hours You might want to keep in mind that time is stored as a decimal fraction, which is usually not an exact representation of the rational fraction (e.g. 11/60 for 11 minutes expressed as a fraction of an hour). So you might want to incorporate some rounding to ensure that "what you see is what you get", just in case you compare your converted time to the constant 38.25, for example. To that end, you might be happier with: =INT((B2-A2)*24*60)/60 Replace that use of INT with ROUND((B2-A2)*24*60,0)/60 if you prefer to round any fractional minutes. You can replace 24*60 with 1440. (Nitpick: Technically, that might still not entirely accurate in some cases, compared to the constant that you might enter manually. But I think it is as good as it gets.) ----- original message ----- "Marsh" wrote: =(b2-a2-int(b2-a2))*24 "Marsh" wrote: Excel 2007, in column A I have 5 rows of Time(in) In column B is Time(out). The data is formatted to show AM, PM In C2, the elapsed time is calculated for row 2, for example =B2-A2, and fomula is filled down to C. This is formatted [h]:mm, yielding an elapsed time (for example 6 hours 45 minutes displayed as 6:45) At the bottom of the data in C I add them up to get the total elapsed time. If the this adds to 38:15 (38 hours 15 minutes), how can I format this to display as 38.25 hours Thank you Marsh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert or format an elapsed time to show mm:ss | Excel Discussion (Misc queries) | |||
Rounding (decimal) elapsed time | Excel Worksheet Functions | |||
Format cells for elapsed time in mmm:ss? | Excel Discussion (Misc queries) | |||
elapsed time displayed as a decimal | Excel Worksheet Functions | |||
Format elapsed time in days? | Excel Worksheet Functions |