Format for Time Elapsed in Decimal

M

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
 
R

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
 
J

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

Marsh said:
=(b2-a2-int(b2-a2))*24


Marsh said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top