Times to Decimal Hours

[AHM]

Hi folks
Am looking to calculate hours: mins into decimal hours and am struggling
big-time. Can anyone point me to an URL that details this (I have had a
good look around sites but unless I missed it cannot find how to crack it).

Problem is:

A B C D E F
1 08:15 19:30 00:30 10:45 9.0 1.75


where A = start time, B = end time, C = lunch time, D = hours worked (all on
hh:mm format)
E = normal hours worked (decimal), F = hours in excess of 9 (E) ion decimal

Any help would be gratefully appreciate

Cheers

Alan

PS. Remove *no spam* from return address if replying directly

 

[Norman Harker]

Hi Alan!

Formula for D1:

=IF(D1*24>9,9,D1*24)

Formula for E1:

=IF(D1*24>9,(D1*24)-9,0)

But if you want the result in time format, divide both by 24 and
format as time.

=(IF(D1*24>9,9,D1*24))/24
and
=(IF(D1*24>9,(D1*24)-9,0))/24


For time generally see:

Chip Pearson:
http://www.cpearson.com/excel/datetime.htm#AddingTimes

 

[Norman Harker]

Hi Alan!

Correction for cells to put formulas in:

E1:
=IF(D1*24>9,9,D1*24)

F1:
=IF(D1*24>9,(D1*24)-9,0)

Otherwise as before.