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 

The formula could work great, but has complications when using it in the document i provided.

I assume that is why you decided to create a second document and use percentages instead of formulas to obtain number values. (Which defeats the purpose of this formula if its not usable in the provided documents format). 

 

Its works fine if the PREP(T), WELD (T), or CLEAN(T)  value is provided as a single number.

But DOES NOT work if a formula is needed to obtain this number (which it is in every case). 

It will continue to calculate outside working ours and into weekends which defeats the purpose entirely.

 

Also for the COMPLETED date portion I was requesting a formula to add +1 day to the CLEAN(T)  value (with the time value always landing at 7:00 am) if possible. 

 

Is anyone able to help me make this work?

Resources