Tracking Time

[CCS]

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 ful
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+MINUT
(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 no
take his/her break/lunch) it does not give the gained minutes but leave
it as 0.

Please assis

 

[Fred Smith]

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?