I need to pull a report from ADP every day and calculate who took a lunch break less than 30 minutes (see table below). I want to use an IF statement that says if the break time was less than 30 minutes, return a YES. The problem is that when I calculate, some 0:30 results return yes and others return no. I tried formatting the times in "h:mm" but my IF statement still return mixed results. I cannot format as general because some 0:30 results show as 0.020833333333 and others show as 0.023611111111. I also tried using CEILING and FLOOR but the results are not consistent.
I feel like the solution should be easy but it is escaping me.
I feel like the solution should be easy but it is escaping me.
| ADP Verification.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | K | L | M | N | O | |||
| 3 | 8/16/2022 | 7:55:00 AM | 12:00:00 PM | 12:30:00 PM | 4:25:00 PM | 8 | Yes | ||||||
| 4 | 8/16/2022 | 8:32:00 AM | 12:30:00 PM | 1:00:00 PM | 5:02:00 PM | 8 | Yes | ||||||
| 5 | 8/16/2022 | 7:55:00 AM | 12:00:00 PM | 12:30:00 PM | 5:00:00 PM | 8.58 | Yes | ||||||
| 6 | 8/16/2022 | 7:00:00 AM | 11:30:00 AM | 12:00:00 PM | 2:00:00 PM | 6.5 | Yes | ||||||
| 7 | 8/16/2022 | 8:30:00 AM | 12:30:00 PM | 1:00:00 PM | 4:00:00 PM | 7 | Yes | ||||||
| 8 | 8/16/2022 | 7:54:00 AM | 12:00:00 PM | 12:30:00 PM | 4:24:00 PM | 8 | Yes | ||||||
Sheet1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O3:O8 | O3 | =IF(H3-G3<"0:30","Yes","No") |
