Forum Discussion
Formula to format cell color based on time ranges (not dates)
Hello,
I'm hoping you can help me. I work for a non-profit organization and I'm trying to figure out a conditional formula based on an (old) Attendance RX report. I want to add work hour information per employee (italics below) and then use the information to show color ranges, based on three 5 minute increments to show the degree of lateness or if the same if the employee punches out early. Employees have varying start and end times.
The Attendance RX report output shows the information in the Time column as 8:45 AM or 1:00 PM (with the seconds there but not showing), etc. I don't know how to change the cell color based on the time ranges. Is this even possible to do?
Example replicated from the Attendance RX spreadsheet.
The employee punched in 45 minutes late and 30 minutes early on 10/23/2024.
The employee punched in one minute early and 35 minutes early on 10/25/204.
Work Hours Date Time
8:30 - 1:30 10/23/2024 8:45:00 AM
10/23/2024 1:00:00 PM
10/25/2024 8:29:00 AM
10/25/2024 12:55:00 PM
Please let me know if there a way to do this.
Thank you!
Sean
My email is email address removed for privacy reasons.
10/25/2024 8:29:00 AM
8:29:00-8:15:00=00:14:00
14 minutes
rather than 35 minutes?
Thank you for reviewing my question.
My goal is to have Excel "judge" how late after the employee's start time he/she punched in. In the example, she punched in one minute early at 8:29. Other times she punched in late. Moreover, she punched out early at 1:00 rather than at 1:30, the time her shift ends. I hope this makes sense.
In this example, with the employee start time at 8:30, she is early so there wouldn't be any penalty points accrued. If she punched in at 8:45, she would have 4 points accrued.
I'm reviewing the point structure with my supervisor but it may be:
0 to 3 minutes grace time.
4 minutes to 8 minutes = 1 point.
9 minutes to 12 minutes = 2 points.
13 minutes to 15 minutes = 3 points.
Over 16 minutes = 4 points.
Employee start and end times vary so I would enter these in separate columns on the first line of each employee.
Thanks.
See the attached workbook.
Thank you SnowMan55 for taking the time to respond. I'll take a look at your solution.
SQ
Snowman55,
In the formulae you created, would I need to enter the In and Out column information? I'd like to enter the in / out time on the first cell of each employee's list of in and out times for the week. These two times would be used to compare all the start and end times punched in
Is there a way to modify the formula to check the In time entered (8:30 for this employee) and then turn the punch in time cell green for punching in prior to or at the start time with a three minute grace time--8:38. the yellow color for times, starting at 8:39 to 8:42, and red for times past 8:43?
I would like to do the same thing with punch out times at the end of the day for those who punch out early.
The report from the software provides employee names. each work date, punched in time, punch out time for lunch, punch in time for after lunch, and the final punch out. In the example below, his work day is 8:00 - 5:00 with an unpaid half hour lunch break.
Name Date Time
8:00 5:00 (I would enter these times for each employee.)
Jones, Michael 10/21/2024 8:45 AM (the :00 for seconds is not shown)Jones, Michael 10/21/2024 11:23 AM
Jones, Michael 10/21/2024 11:45 AM
Jones, Michael 10/21/2024 5:02 PM
Jones, Michael 10/22/2024 8:00 AM
Jones, Michael 10/22/2024 11:55 AM
Jones, Michael 10/22/2024 12:23 PM
Jones, Michael 10/22/2024 4:55 PM
I hope this makes sense.
Sean
would I need to enter the In and Out column information?
Did you read the notes and referenced material on the _Info worksheet? ("Similarly, there should be an indicator for each clock-in/clock-out time for each such action.") If you do not create something like an In/Out indicator as needed (whether manually or by cell formulas in a helper column), how do you expect the CF formulas to determine which entries are punching in for the day, and which are punching out for the day? (And consider the "special cases" where an employee skips lunch for a day, or takes an additional off-clock break to care for a child, etc.)
I'd like to enter the in / out time on the first cell of each employee's list
Putting two data values (the standard clock-in time and the standard clock-out time) into one cell is bad design. That would require more complicated CF formulas, as I noted.
I'll not respond to the other questions until I am sure you have read that _Info worksheet content.
