Hi, I am looking to utilize conditional formatting to color code a set of totals compared between 2 payroll systems. The one payroll system pays by the minute and will have the format 37:47 for example to show 37 hours and 47 minutes worked that week. The other payroll system rounds to the nearest quarter.
There is a possibility that the overall total could be off anywhere up to .75 hours between the two systems. Here is an example that illustrates that:
Payroll System 1
Let's say an employee works 7 days and every one of those days, he worked 8 hours and 7 minutes. This payroll system since it pays by the minute will ultimately end up with 56:49 or 56.75 hours
Payroll System 2
in the same scenario where the employee works 8 hours and 7 minutes every day, this system since it rounds to quarters is rounding all of these days down to 8 hours each day and ends the whole week with exactly 56 hours
Since discrepancies between the two systems are inevitable, I am looking to utilize the color coding as I mentioned above to compare the totals between both systems. If the totals are within .75 of each other, I want it to color the cell yellow to mean that this one does not need checked because the hours totals are close enough to each other. Be advised that I did use a rounding formula for the payroll system 1 data to convert it to quarters to more easily compare it to payroll system 2.
What formula would I use to do the color coding within .75 of each other? I imagine it is a greater than and less than formula, but I am not sure how that would go.
my data totals that I am comparing for conditional formatting are D2 and S2 if that will help for reference.
There is a possibility that the overall total could be off anywhere up to .75 hours between the two systems. Here is an example that illustrates that:
Payroll System 1
Let's say an employee works 7 days and every one of those days, he worked 8 hours and 7 minutes. This payroll system since it pays by the minute will ultimately end up with 56:49 or 56.75 hours
Payroll System 2
in the same scenario where the employee works 8 hours and 7 minutes every day, this system since it rounds to quarters is rounding all of these days down to 8 hours each day and ends the whole week with exactly 56 hours
Since discrepancies between the two systems are inevitable, I am looking to utilize the color coding as I mentioned above to compare the totals between both systems. If the totals are within .75 of each other, I want it to color the cell yellow to mean that this one does not need checked because the hours totals are close enough to each other. Be advised that I did use a rounding formula for the payroll system 1 data to convert it to quarters to more easily compare it to payroll system 2.
What formula would I use to do the color coding within .75 of each other? I imagine it is a greater than and less than formula, but I am not sure how that would go.
my data totals that I am comparing for conditional formatting are D2 and S2 if that will help for reference.
Last edited:
