Forum Discussion

Anonymous's avatar
Anonymous
Oct 11, 2019
Solved

Calculating job estimates within working days and hours (in Excel). Help Please!

Hello,  I'm currently having a lot of trouble calculating estimates within working hours in excel.  Ill try to explain to the best of my ability what i am trying to accomplish. Attached is my curr...
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Oct 22, 2019

    Deleted 

    In the attached version of your file, the formula in L2, copied across to N2, is: 

    =K2+INT(D2/7.5)+MOD(D2,7.5)/24+
    IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),
    0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24+
    2*(K2+INT(D2/7.5)+MOD(D2,7.5)/24+
    IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),
    0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24>
    INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)

    In O2, the formula is: 

    =WORKDAY(N2,1)+
    7/24

    I hope that the foregoing formulas will now return your expected results, as shown here: 

     

Twifoo's avatar
Twifoo
Silver Contributor
Oct 12, 2019

Deleted 

In the attached file, the formula in G2, copied across to I2, is: 

=F2+C2/24+
1/24*((MOD(F2,1)<12/24)*(MOD(F2+C2/24,1)>12/24)*(MOD(F2+C2/24,1)<15.5/24))+
16.5/24*((MOD(F2,1)<12/24)*(MOD(F2+C2/24,1)>15.5/24))+
15.5/24*((MOD(F2,1)>12/24)*(MOD(F2+C2/24,1)>15.5/24))+
2*((MOD(F2+C2/24,1)>15.5/24)*(WEEKDAY(F2+C2/24)>5))

I hope that the idea of the foregoing formula will suit your needs. 

Anonymous's avatar
Anonymous
Oct 21, 2019

Twifoo 

 

You can see how this solution was unsuccessful in the attached document. 

(Its calculating out of working hours and on weekends).

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Oct 22, 2019

    Deleted 

    In the attached version of your file, the formula in L2, copied across to N2, is: 

    =K2+INT(D2/7.5)+MOD(D2,7.5)/24+
    IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),
    0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24+
    2*(K2+INT(D2/7.5)+MOD(D2,7.5)/24+
    IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),
    0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+
    (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24>
    INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)

    In O2, the formula is: 

    =WORKDAY(N2,1)+
    7/24

    I hope that the foregoing formulas will now return your expected results, as shown here: 

     

    • Anonymous's avatar
      Anonymous
      Oct 22, 2019

      This formula works great thank you very much!
      If I wanted to apply this to say two or three workers which values should i be switching out in the provided formula if possible? I've tried swapping out all of the 7.5 values in the formula for 15 but it doesn't seem to be working. I assume I may be approaching this the wrong way?

       

      Example:
      1 worker = 7.5 working hours/day
      2 workers = 15 working hours/day
      3 workers = 22.5 working hours/day

      Attempted conversion to 2 workers example:

       

      =K2+INT(D2/7.5)+MOD(D2,7.5)/24+
      IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),
      0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+
      (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+
      (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24+
      2*(K2+INT(D2/7.5)+MOD(D2,7.5)/24+
      IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),
      0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+
      (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+
      (MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24>
      INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)

      converted to:

      =K2+INT(D2/15)+MOD(D2,15)/24+
      IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),
      0,15.5),CHOOSE((MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>7/24)+
      (MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>12/24)+
      (MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),0,1,16.5))/24+
      2*(K2+INT(D2/15)+MOD(D2,15)/24+
      IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),
      0,15.5),CHOOSE((MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>7/24)+
      (MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>12/24)+
      (MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),0,1,16.5))/24>
      INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)

       

      I assume I'm doing this the wrong way.
      Are there specific areas within this formula that i may be able to substitute values to calculate these days with 2 or even 3 workers instead of one?

Resources