Format for Time Elapsed in Decimal

[Marsh]

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

 

[T. Valko]

Multiply by 24:

A1 = 38:15

=A1*24

Format as General or Number

Result = 38.25

 

[Ron@Buy]

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]

=(b2-a2-int(b2-a2))*24

 

[Joe User]

:
Subject: RE: 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....


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 -----

=(b2-a2-int(b2-a2))*24

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