Excel Cell Formula: Intermittent Error Comparing Calculated vs. Manually Entered Time Values

Question

NOTE: I have verified several times by several different methods listed here on StackOverflow that these cells contain values not text!

I am trying to write a scheduling workbook that can tell me whether or not my current staffing is adequate. There is one schedule sheet with each staff member's clock in, 1st break out, 1st break in, lunch out, lunch in, 2nd break out, 2nd break in, and clock out time punches on individual rows, and those eight (8) columns repeat seven times (x7) to account for each day of the week. There is another "analysis" spreadsheet that has a 15 minute increment on each row starting from 6:30 AM - 6:45 AM on Monday to 5:45 PM - 6:00 PM on Sunday.

On the analysis sheet, I have tried using different formulas to increment the Block Start and Block End times by 15 minutes, then fill down to account for every block over those seven days. The formulas I remember trying are as follows assuming I manually entered 6:30 AM in cell B2:

• =B2+(15/60/24)
• =TRUNC(B2+TIME(0,15,0),12)

The problem becomes especially painful when I try to compare whether the staff's scheduled start time is greater than or equal to the block's start time, as well as when I try to compare whether the staff's scheduled end time is greater than or equal to the block's end time. I believe this is due to minor millisecond differences in the serial value of the calculated time versus the serial value of the manually entered schedule time. However, the goal is to run the schedule through a =COUNTIFS() formula to determine the total staff present during that block's time. So far the formula I have come up with for that looks like this (please ignore the column references because I copied it down several columns to be able to try a few other methods in those first columns):

=COUNTIFS(OFFSET('Front Desk'!$B$5:$B$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<>",OFFSET('Front Desk'!$B$5:$B$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<="&'Front Desk Analysis'!H2,OFFSET('Front Desk'!$C$5:$C$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<>",OFFSET('Front Desk'!$C$5:$C$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),">="&'Front Desk Analysis'!L2)+COUNTIFS(OFFSET('Front Desk'!$D$5:$D$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<>",OFFSET('Front Desk'!$D$5:$D$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<="&'Front Desk Analysis'!H2,OFFSET('Front Desk'!$E$5:$E$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<>",OFFSET('Front Desk'!$E$5:$E$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),">="&'Front Desk Analysis'!L2)+COUNTIFS(OFFSET('Front Desk'!$F$5:$F$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<>",OFFSET('Front Desk'!$F$5:$F$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<="&'Front Desk Analysis'!H2,OFFSET('Front Desk'!$G$5:$G$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),"<>",OFFSET('Front Desk'!$G$5:$G$24,0,(MATCH(A2,'Front Desk'!$B$3:$BE$3,0)-1)),">="&'Front Desk Analysis'!L2)

Please bear with me on the length and somewhat repetitiveness because I am trying to account for either of the four intervals (clock in - 1st break out, 1st break in - lunch out, lunch in - 2nd break out, and 2nd break in - clock out). I think it is necessary to disclose the function's length and that it will get longer because I need to account for some staff that may or may not take breaks and/or lunch during their entire day (imagine somebody with a short 2 to 4 hour shift would just clock in and out once). I'm worried about hitting the function limit if I start adding a whole bunch of complex nested ROUND() functions in there.

If I had to sum this issue up, I guess I have four questions:

1. Why does the 15th decimal digit in the serial representations for 9:30 AM and 10:30 AM differ by one (1) from the serial value of the manually entered time, but the decimal expansion of the difference between the two doesn't? (0.395833333333334000000 - 0.395833333333333000000 = 0.000000000000000000000)
2. Since the difference between the two values apparently evaluates to zero, why does the comparison evaluate to false?
3. If you're going to make the argument that this is a floating point calculation issue, why wouldn't there be less TRUE comparisons (since each of the calculated values is calculated based on the previous value compounding the difference for all subsequent values) making that result the exception instead of the norm?
4. Slightly off topic from the last three, but is this approach similar to the method you would use to solve the overall scheduling issue, or what would you suggest to solve the problem?

I am equally interested in the answers to each of those questions, so please feel free to respond to one of them or all (Please indicate the relevant question # in your response). Thank you all in advance!

EDIT: I'm new to asking questions here on stack overflow, but I believe a minimum reproducible example could be created if you follow these steps:

1. Type "6:30 AM" + Enter key in cell B2.
2. Type "=B2+(15/60/24)" + Enter key in cell B3.
3. Click Format Painter button, then click cell B2, then click cell B3 (formats the formula cell as time if step 1 was executed correctly, otherwise click General from the format drop down then click Time).
4. Select cell B3, then drag down until the formula equals 9:30 AM or greater.
5. Type "9:30 AM" + Enter key in the C column cell adjacent to the B column cell that also contains the value "9:30 AM" + Enter key (should be row 14 I believe).
6. In the D column cell adjacent to the right of those cells type this formula: =B14=C14 + Enter key and you should get a FALSE evaluation.

Answer 1

You are running head-on into the age old problem of Excel's data/time system, which as you know relies on inaccurate floating point math.

The solution has always been to round to a magnitude that avoids the errors:

=ROUND(B5,7)

The above will round time values to the second, allowing close values to be equated, and one second precision should be accurate enough for employee scheduling and payroll.

Now using lots of ROUND() functions in a formula over and over again is far from ideal.

There are a couple of workarounds, neither are ideal.

1.) Have a sheet that replicates your source data, but by using a ROUND() function for each corresponding data point. Under this scenario, you would aim your COUNTIFS() formulas to this intermediary sheet. This gets you past having to use ROUND() a zillion times within each COUNTIFS(). It is true that ROUND() is still being used on each data point... but it is only being used ONCE for each data point.

2.) Utilize VBA to do your calculations and return the results to your analysis sheet. This could even be configured as a User Defined Function (UDF) that you could call directly from the analysis sheet as a formula.