Tracking Time
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 |
Tracking Time
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 |
All times are GMT +1. The time now is 07:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com