Calculating hours between two times

I am having trouble with a formula to calculate the hours worked between two times,

I have formatted the cells containing time as 'time'.

The formula I am using is =TEXT(C7-D7,"h") C7 being start time and D7 being finish time.
The formula is working to a certain extent but stops working when 22:00 is in the 'finish' column but works perfectly when in the 'start' column??

The error is coming through as 'Value' and is saying that 'a value used in the formula is of the wrong data type'



Any help would be much appreciated,

Thanks,

It looks to me rather like you're trying to deduct the finish times from the start times, which is the opposite of what you should be doing. You could use:
=(D7<C7)*12+D7-C7
and simply format E7 for hh:mm. That would preserve the numeric aspect of the data.

That seems to have worked, thank you!

I have one more question though - is there a way of formatting the hours so that they read 8.5 rather that 08:30 for eight and a half hours?

Thanks again

Try =((D7<C7)*12+D7-C7)*24 and format as number

worked perfectly, thank you

Glad it helped. But do you understand why it works?

convert hours time to dec number

jbashir said:

is there a way of formatting the hours so that they read 8.5 rather that 08:30 for eight and a half hours?

Click to expand...

try