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....
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?
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....
This is perfect! Exactly what I was trying to accomplish. You're brilliant, thank you.
Glad it works for you because honestly it was a bit of work to get all those formulas together. That said, not to be picky, but maybe you could mark that response as the solution so future viewer will see that instead of you question as the solution.
again, so happy it is working for you.