Forum Discussion

Deleted's avatar
Deleted
Jul 10, 2019

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...
date
excel
Formulas and Functions
quotes
Time
Workday
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 10, 2019

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

Deleted's avatar
Deleted
Jul 11, 2019

SergeiBaklan 

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. 

 

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 11, 2019

    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:

     

    • Deleted's avatar
      Deleted
      Jul 25, 2019

      SergeiBaklan 

      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.

       

       

       

Resources