Forum Discussion
Trouble quoting date+time(hour/mins) of project start date/completion using the WORKDAY function.
Deleted ,
Perhaps something like
=IF($I2="","", INT(WORKDAY($I2,$E2)+MOD($I2,1)+MOD($E2,1))+ MEDIAN(MOD(WORKDAY($I2,$E2)+MOD($I2,1)+MOD($E2,1),1),7.5/24,15.5/24) )
in attached file for Completion Date (1W) column
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:
I would like the option to choose a custom start time/date for the job as well.
But when i remove the cell value from the start date column (I) for example (=k2) it seems to still be calculating weekends.
The shift is from 7-3:30 but there is a 1 hour lunch break within making the shift 7.5 working hours for a single employee and 15 hours for two employees.
I need a separate formulas for calculating the estimated completion dates of jobs (within working hours) for 1 worker and 2 workers.
Please help.
EX:
ONE WORKER (7.5 working hours per day)
[J2] Completion date/time (During weekdays only calculating within the shift hours of 7am-330pm)=
[I2] Date/time started (WEEKDAY within the hours of 7am-330pm) + [D2] Total Hours to complete job.
TWO WORKERS (15 working hours per day)
[K2] Completion date/time (During weekdays only calculating within the shift hours of 7am-330pm)=
[I2] Date/time started (WEEKDAY within the hours of 7am-330pm) + [D2] Total Hours to complete job.
This worked almost perfectly! Thanks for the very quick response.
It seems to be calculating Saturdays and Sundays though.
Any ideas on how to remedy this and keep it to strictly Mon - Friday?
Document attached has the formula suggested integrated in columns 1w and 2w.