Forum Discussion
Solved
Projected Overtime hours based on current hours worked and remaining hours to work.
We're starting to phase out overtime at our work site and i'd like to create a spreadsheet that breaks down what hours have been worked by each employee, how many hours are left to work and how many hours of overtime each employee is projected to have so we're able to plan ahead to reduce overtime.
I've made some progress on my own, but just can't quiet accomplish what i'm trying to do.
you would enter it if the downloaded data file doesn't include it. Sometimes that is included in the header (or footer) of the file.
in the attached I went 'overboard' and used the scheduled shifts for each individual to determine the INDIVIDUAL total scheduled hours that week and estimate their individual total hours for the week based on the download date (if not entered it will use NOW()).
To do this I created a heavy function to parse that day time code and output a string for which days they are scheduled to work where M-Th,Su is changed to: "MTWH--U" where each day with a letter they are scheduled (note Th=>"H" and Su=>"U") and days they are not scheduled shows a "-". Then I also added the Start and End times for their shift and if past midnight it has the +1 day already built in so End-Start will always be positive.
I then calculate the total scheduled hours (instead of the fixed 40hrs)
I also estimate total week hours based on hrs downloaded + future days scheduled and ignore today if it is already past their shift today.
Note the overtime column right now is actual OVER SCHEDULED time but if you want actual Overtime based on 40 hours that is easy fix of the formula being IF(est_time>40, est_time-40,"").
I hope this works for you....
10 Replies
Let's see if I can explain this better.
Aliana currently has 24.03 hours as of 7am today, she's 5 hours into her shift, with 3 hours remaining, plus her scheduled 8-hour shift tomorrow. So, she's projected to work a total of 40.03 hours.
Diana Caldwell is currently at 32.67 hours as of 7am today, she's also 5 hours into an 8-hour shift, plus her schedule 8-hours tomorrow. That would bring her to a total of 48.67 hours.
In column E I want to show the total projected hours by adding column B with elapsed time, remaining time and remaining days/hours to show total hours in column E and then total hours over 40 in column F
ok so ignore my prior sheet as it appears you are getting into the partial day shift today but based on this latest post you are ignoring it.
Based on this latest post you are talking about 5 hours into an 8 hour shift ... but the calc you are doing is simply prior time + potential hours today and rest of week.
Maybe you want to use data upload time instead of 'now' to estimate if today's shift should be added or not?
How would show data upload time?
not sure if this is what you want but fixed a few formulas to be more in line with what I think was intended by the titles and added a couple lines.
some key points to note is the DATE/TIME VALUES are based on days as a unit of measure so hours are fractions of a day and TODAY() is number of days since 1900. So you need to convert accordingly when needed.
EDIT: I forgot to add a couple things.
a) for the total allowed hours (40 hours) that is NOT good practice to have as a fixed value next to lookup values. basically if another employee is added the lists in columns A and B will shift but those max hours will not.
b) I tried to account for 3rd shift hours but the calculation for # hours left at this minute does not take that fully into account (i.e. if you are looking at this sheet at 5am and their shift is 10pm-6am it will be wrong)
c) also note that functions like TODAY() and NOW() will only be update on events and so let's say you open it when you get in and look at it later or tomorrow, if you don't do something (e.g. hit 'enter' on a cell in the sheet) those numbers will not be updated
What would you suggest I do with column C, would simply moving it be enough?
This is getting close to what I wanted. Was struggling to get the Hours left, so that was helpful.
I'm also having issues getting Current hours from column B and adding that with hours left and elapsed time for the day. When I try to add them together, I get a 45645.4 displayed in column E.
Column B isn't a live tracker, it only "Updates" after an employee has clocked out for the day, which is the reason for adding elapsed time, remaining time and current hours.
Not sure I follow what you are trying to achieve but most of the 'hours' in your schedules were in fact texts. I've fixed that and now it seems you formulas are calculating something. As said, I don't really understand them.
See attached.
