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

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

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

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

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