I have a spreadsheet that I want to have track time on lunch break. D4 is the clock in time for the day, E4 is the clock out time for lunch, F4 is the clock back in from lunch time, and G4 is the formula subtracting the clock out from the clock in times. All of these are shown in the formula bar as 12:00:00 PM for instance but are formatted to show just 12:00 in the actual cell. I have $AU$1 (which shows 0:30) that I am trying to use in a formula that counts the cells that are over 30 minutes from lunch. I'm trying to use this formula but it is also counting the cells that are equal to 30 minutes... can anyone HELP me??? I have all the cells' categories set as "time" and "13:30" if that matters.
=IF(ISBLANK(D4),"",COUNTIFS($G4,">"&$AU$1))
Thank you so much!! ~ Katherine
Hello,
In G4, use ROUND using 10 decimals. Ex. =ROUND(F4-E4,10)
Br,
Anders
Hello,
If you also need to compare if the lunch break equals AU1 (30 minutes) then you will have to do same rounding on that value too.
AU1 00:30 in decimal format is with 16 decimals 0,0208333333333333 while the calculated time difference 00:30 is 0,0208333333333334. So when lowering the number of decimals on one value you will not get them to be equal.
Excel is fun! 🙃
