Forum Discussion
Formula to format cell color based on time ranges (not dates)
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.
SnowMan55,
Thank you for the follow up. As you can guess, this is new Excel territory for me, as is asking for help via this forum.
I didn't see the notes, my apologies.
Thank you for your assistance.
Sean
Gee, I feel like a dope for not seeing the Info tab. D'Oh! Again, my apologies.
SQ
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
Can the formula add [store] a number to a column
Not "the" formula (the workbook shows you three CF formulas, after all), but yes, a cell formula (or set of cell formulas) in an unused/new column can do that. (A CF formula cannot change a value; it can only change the appearance.) That would go into the helper column that I mentioned.
Once again, you need identifiers for which times are clock-in for the day and which are clock-out for the day; only those rows should get the absence points. Think about how you are going to get/create that In/Out identifier (manually, or hopefully by cell formulas).
An additional relevant question for you: Do any people (among those that could be listed in your report) work a shift that could cause them to clock out after midnight?
I was going to use the colors to add the points
That plan was faulty from the start: Neither cell formulas nor CF formulas can select or summarize cell values depending on fill color. (Well, not unless they involve a custom VBA function, which is a whole other ball of wax that you should not attempt.)
The report from the software alphabetically lists each employee's punches
That is perhaps relevant information. It suggests that the employee data are not segregated by employee. Or do you segregate the data by employee (onto different worksheets or different columns...) when you make a (modified) copy of the report in Excel?
The lunch punch times vary but should not exceed a half hour.
That "should not" suggests that you will want to derive absence points for the lunch break also. True?
Is there a way to send the file to you?
Attaching a file to a post/reply by using the paper clip icon should work. Are you sending an Excel file or some other-format file? I suggest you try it again, as there apparently has been a restriction on very new forum members (am not sure what this new forum software is doing). But yes, you can send a file via private messaging: While you are signed in to the forum, click on any Snowman55 avatar to see my profile page, then click the Message button on the right. The resulting "popup" for constructing a message includes a paper clip icon also. (If you don't see a Message button on this page, you have been signed out; click the Sign In link at top right.)
Or you can put the file on Microsoft OneDrive or Google Drive or DropBox or a similar file-sharing service, set the properties so that everyone can read it, and send/post a link to the file.
Be sure to remove or replace any confidential/sensitive/proprietary information.
SnowMan55
I do appreciate your assistance and patience.
Perhaps this project is beyond my ability, but I'll try again. I was able (this time) to attach an abbreviated report from the software. I would use this report and enter the scheduled start time (one cell) and the end time (next cell) for each employee and will have to do the same every two weeks when the timecard information is submitted.
Using colors wasn't the best idea when, in the end, I have to tally points, which will be used for conversations with the employees who are continuously late.
In the example provided, I entered the information in the yellow cells.
The points entered in L4 would look at the actual employee start time and compare it to the scheduled start time (B5). L8 would look at the actual end time and compare it to the scheduled end time.
To answer your lunch questions, honestly, I don't know how to handle the 30 minute lunch break. I may have to manually review the times to see if there's a trend of employees taking extended lunch breaks.
All employees work between 7:30 am and (generally) no later than 6:00 pm.
I hope this answers your questions.
Again, thank you for your assistance. This is new Excel territory for me. I'm looking for a way -- if possible -- to reduce this tracking activity to I can have more time to assist people when they come in for clothing and other needed resources.
Sean
tried to attach a file that shows the raw data the software program exports but the paperclip feature isn't working
https://filetransfer.io/
