Time differances

T

Trever B

1 have 4 boxes

start time end time lunch hours worked
09:00 17:00 1.00 7:00
09:00 17:00 0.30 7:70 (should
be 7:50)

(B3-A3)*24-C3

How do I make it 9.5 hours by changing forumla

Thanks
 
E

Earl Kiosterud

Trever,

=(B3-A3)*24-C3*24

Format for General, Number, or anything except Date/Time. Format - Cells -
Number.

You'll get 7.5. If you got 7:50, as you requested, it would look like hours
and minutes.
 
D

David McRitchie

Hi Trevor,
Time is entered using colons to separate minutes from hours,
DO NOT USE A DECIMAL POINT where you should be using a colon.
A value of 1.0 represents 24 hours not one hour..
A value of .3 represents 7 hours 12 minutes

The formula to return decimal hours is:, formatted as 0.00
c2: =(b2-a2+(a2>b2))-c)*24
and fill down.with the fill handle.

If you format the result as time [h]:mm
The logical expression (A2-b2) will return either 0 or 1,
where a value of 1 represents 24 hours (1 day) and is necessary
if hours go through midnight.

The format of [h]:mm prevents hours from rolling off into days
and not being shown, you will probably need that format for
the total number of hours, if shown as an Excel time.

For more information on Date and Time see
http://www.mvps.org/dmcritchie/excel/datetime.htm
 
Top