Trouble quoting date+time(hour/mins) of project start date/completion using the WORKDAY function.

Deleted
Not applicable

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 with a system that works for the most part but I'm having some issues relating to the WORKDAY function. 

 

My goal is to have an input start date/time (ex. Wednesday July 10, 2019 7:00 AM) which in turn will generate accurate information of the completion date/time for the job and simultaneously calculating when the next job will start/finish (start date/time is the finish date/time of the previous job).

 

Ex: Start: Wednesday July 10, 2019 7:00 AM    -   Finish: Thursday July 11, 2019 2:55 PM.

      Start: Thursday July 11, 2019 2:55 PM        -   Finish: Friday July 12, 2019 9:45 AM.

 

  So far I've been able to calculate the finish "date" but I'm having troubles with the information generating the "time" of completion and therefore the next jobs start date/time (specifically time) correctly. The system seems to be generating the "day of completion" fine but the "hours/mins" are not calculated into the equation. I would like the WORKDAY function to be set to 7:00am - 3:30pm but I'm not sure if this is possible.

 

The document is referring to a chart which calculates the approximate time it takes to complete a specific job depending on the amount of "parts"and "blades".

(ex: (#of parts * #of blades) * Time Per Blade = Total time (0.0 days) .)

 

The total time is then used in the workday calculation:

WORKDAY(start_date, Total Time, no holidays).

 

I'm not sure if I'm using the WORKDAY function incorrectly or not.

The end goal is to have it calculate the date and time of day when a job will be competed, then in turn generating the next jobs start time.

 

Attached is the document for better clarity. Any help would be appreciated.

I'm fairly new to excel so spare me the criticism if this is a common error.

Thanks!

 

5 Replies

@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

@Sergei Baklan 

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. 

@Sergei Baklan 

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:

image.png

 

@Sergei Baklan 

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.