Forum Discussion
Projected Overtime hours based on current hours worked and remaining hours to work.
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....
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?