Forum Discussion
Trouble quoting date+time(hour/mins) of project start date/completion using the WORKDAY function.
Hello, I'm currently tasked with completing an excel document that accurately quotes job start and completion dates (including time of day within a work shift). So far I've been able to come up wit...
I actually noticed that the formula isn't quite calculating the hours correctly.
Eg. Job 9083 has a total of 10.93 hours to complete.
When start time is say July 1 (at 7:00 am) the job should be ending approximately at 10:30 am Tuesday which is 11 working hours later (within the 7-3:30 shifts).
Monday - 7.5h (full shift) +
Tuesday - The remaining 3.5 hours, should be finishing around 10:30 am.
It seems to be calculating the job to finish at 3:30 rather than 10:30.
Deleted
Yes, you are right, sorry for that. Corrected formula
=IF($I2="","",
INT(
WORKDAY($I2,INT($D2/ShiftLength))+
MOD($I2,1)+
($D2-INT($D2/ShiftLength)*ShiftLength)/24)+
MEDIAN(
MOD( WORKDAY($I2,INT($D2/ShiftLength))+MOD($I2,1)+($D2-INT($D2/ShiftLength)*ShiftLength)/24,1),
ShiftStart/24,
ShiftEnd/24)
)
I only took 8.5 hours shift length - 3:30pm minus 7:00AM gives 8.5. To add bit more flexibility here are named parameters: