I am trying to use the "if" formula, in column N in the attached screenshot, to determine whether employees are entitles to a meal premium.
=IF(AND(AND((G26-F26)>TIME(6,0,0),(G26-F26)<TIME(10,0,0)),M26<TIME(0,30,0)),"YES",IF(AND(AND(L26>TIME(6,0,0),L26<TIME(10,0,0)),(G26-F26)>TIME(5,59,0)),"YES",IF(AND(((G26-F26)+(I26-H26))>TIME(10,0,0),M26<TIME(1,0,0)),"YES",IF(AND(L26>TIME(10,0,0),(I26-H26)>TIME(4,59,0)),"YES","NO"))))
My question is why is excel returning "Yes", e.g. cell N26, for some employees that worked >6hours but <10 hours and had 29mins break and returns "No", e.g. cell N27, for some employees with the same number of hours worked and break time?
You only get a meal premium if any of the following conditions are true:
- You worked more than 6 hours but less than 10 hours in a day and did not take a break
- You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
- You worked more than 10 hours and took less than 30mins break
- You worked more than 6 hours in a day and did not take a break
- You worked more than 10 hours and took a second break after the 4.59hrs of work (work hour start counting after end of 1st break, which must occur before the 6th hour of work).
- You worked more than 10 hours and took less than 1hour break
F:G
really integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values inF:G
do not correspond with the dates shown inB:C
in all instances.IF
is actually returning aTRUE
.F:G
are showing the serial number only for the date except for Row 40. And in Row 26, theIn
andOut
inF:G
differ by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all inF:G
, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.