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....
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?
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....