1

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"))))

enter image description here

updated excel picture to show time

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
Improve this question
4
  • Are the values in 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 in F:G do not correspond with the dates shown in B:C in all instances.
    – Ron Rosenfeld
    Commented Jun 6, 2018 at 12:43
  • Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
    – Funmi
    Commented Jun 6, 2018 at 12:54
  • There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and which IF is actually returning a TRUE.
    – piko
    Commented Jun 6, 2018 at 12:59
  • @Funmi Actually, in your first picture F:G are showing the serial number only for the date except for Row 40. And in Row 26, the In and Out in F: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 in F:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.
    – Ron Rosenfeld
    Commented Jun 6, 2018 at 13:11

1 Answer 1

Reset to default
0

Because it violates :

•You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.

so N26 pass coz 1221 hrs - 0555 hrs = 0626 hrs

and in N27 fail coz 1106 hrs - 0551 hrs = 0515 hrs

Hope it helps. ( :

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .