Forum Discussion
Formula to format cell color based on time ranges (not dates)
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