Time calculator

[coolhit]

Hi

I am trying to calculate extra hors worked above 7.5 hrs.

In column c2 = start time (custom format hh:mm)
In column d2 = finisha time (custom format hh:mm)
In column e2 = extra hours (formula =(D2-C2)-7.5 and cell is custom format hh:mm) reply i get is #######.

PLease can someone tell me what i am going wrong.

example below

c2= 06:00
d2= 17:00
e2= d2(17:00)- c2(06:00)- 7.5 result should be 3.5hours.

Any help appricated.

Regards

[Ace_XL]

try

=d2-c2-7.5/24

[2709236]

Try this:

e2: d2-c2
f2: 07:30
g2: e2-f2

[coolhit]

Hi

I have used formula =d2-c2-7.5/24.

I have draged this formula downward, unfortunatley in colum c where there is no value formula cell shows #######.

Please advice what amendment is should use if colum c has no value then formula cell stays blank.

I know for vlookup i use =if(iserror(???????????????).

please advice.

Regards

[Ace_XL]

Try

=IF(C2="","",d2-c2-7.5/24)
or
=IF(OR(C2="",D2=""),"",d2-c2-7.5/24)

The latter will give blank when either of C2 or D2 are blank

[Kevin UK]

Hi coolhit

Try this; =IF(COUNTA($C2:$D2)<2,"",MOD($D2-$C2,1)*24-7.5)