Subtracting minutes in hh:mm format, and set hours

[martikaih]

I posted this question before:

I want to subtract a lunch time (not always an hour) and calculate overtime. I want to be able to put my lunch in the hh:mm format - 0:45 for 45 minutes, which I think I've figured out (see below). I also want to calculate overtime by subtracting a blank 8 hours from my total hours cell. How could I accomplish this?

I have all cells in the hh:mm format

I have a1=time in
b1=lunch length
c1=time out
d1=total hours [ =SUM((C1-A1)-(B1)) ]
e1=overtime
----------------------------------------------------------------
and was answered this:

Welcome to MrExcel.
Put this in E1
=MAX(D1-8,0)

Best regards,
_________________
Barrie Davidson
My Excel Web Page
--------------------------------------------------
This is not working for me. I get an error in the formula. Excel either puts a pipe after the cell reference, or deletes the comma and zero after the 8, and I get a bunch of # in the cell.

 

[Barrie Davidson]

How did you input the formula? What value is in cell D1?

That formula should work; it did for me.