Forum Discussion
Formula to format cell color based on time ranges (not dates)
See the attached workbook.
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.
SnowMann55,
Can the formula add a number to a column instead of changing cell color? Using a tardiness scale, if the employee is:
3 to 6 minutes late = 1
7 to 10 minutes late = 2
11 to 15 minutes = 3
16 + minutes = 5
This allows us to tally points for a specific timeframe. I was going to use the colors to add the points but maybe there's a way to automate that step.
The report from the software alphabetically lists each employee's punches in/out for the day as well as in/out for his/her half hour lunch break. I will have to add the employee's scheduled start/stop times. The lunch punch times vary but should not exceed a half hour.
I tried to attach a file that shows the raw data the software program exports but the paperclip feature isn't working. Is there a way to send the file to you?
Thank you
I am reading your notes and trying to comprehend what the formulas do. Thanks for your work on this.
Saving time on this weekly task will free up more time to respond to our clients' clothing and housing needs.
SQ
Thank you SnowMan55 for taking the time to respond. I'll take a look at your solution.
SQ
