This formula is to track the breaks/lunch (time) taken by an employee.
They are entitled to 1hour 30mins total. If they do not take the full
time then the company gains those minutes.
The following formula is being used:
=IF(C16=0,0,IF(LEN(C16)<4,-$P$6,((HOUR(C16)*3600+MINUTE
(C16)*60+SECOND(C16))-$P$6)/60))

Note: (p6) = 1800 and data is entered as hh:mm:ss
The problem is when a zero value is entered (i.e. the employee does not
take his/her break/lunch) it does not give the gained minutes but leaves
it as 0.

Please assist


--
CCS
------------------------------------------------------------------------
CCS's Profile: http://www.excelforum.com/member.php...o&userid=36236
View this thread: http://www.excelforum.com/showthread...hreadid=560180

When you tell Excel if C16 is 0, I want a 0 result, amazingly enough, that's
what you get.

So if C16 is 0, what result do you want? Put that as the second parameter in
your If statement (replacing the 0).

Also, "((HOUR(C16)*3600+MINUTE(C16)*60+SECOND(C16))" can be simplified to
c16*86400.

Also, if employees are entitled to a 90 minute break, why is P6 = 1800
(seconds), or 30 minutes. Why wouldn't it be 90 minutes?

--
Regards,
Fred


"CCS" wrote in message
...

This formula is to track the breaks/lunch (time) taken by an employee.
They are entitled to 1hour 30mins total. If they do not take the full
time then the company gains those minutes.
The following formula is being used:
=IF(C16=0,0,IF(LEN(C16)<4,-$P$6,((HOUR(C16)*3600+MINUTE
(C16)*60+SECOND(C16))-$P$6)/60))

Note: (p6) = 1800 and data is entered as hh:mm:ss
The problem is when a zero value is entered (i.e. the employee does not
take his/her break/lunch) it does not give the gained minutes but leaves
it as 0.

Please assist


--
CCS
------------------------------------------------------------------------
CCS's Profile:
http://www.excelforum.com/member.php...o&userid=36236
View this thread: http://www.excelforum.com/showthread...hreadid=560180