datetimegoogle-sheetsgoogle-sheets-formula

How to add work hours to a date with time?


I am trying to add business hours to some date. The issue is when I set the Start Date during Holidays. For example: I set

Column A3 - Start date - 2020-07-09 8:01
Column B3 - Add hours: 2
Column E2 - Start hour: 8:00
Column F2 - End hour: 17:30
Column G2:G - Holiday: 9/7/2020

I used this formula:

=IF(A3="","",WORKDAY(A3,INT(B3/9.5)+IF(TIME(HOUR(A3),MINUTE(A3),SECOND(A3))+TIME MOD(B3,9.5),MOD(MOD(B3,9.5),1)*60,0)>$F$2,1,0),$G$2:$G)+IF(TIME(HOUR(A3),MINUTE(A3),SECOND(A3))+TIME(MOD(B3,9.5),MOD(MOD(B3,9.5),1)*60,0)>$F$2,$E$2+TIME(HOUR(A3),MINUTE(A3),SECOND(A3))+(TIME(MOD(B3,9.5),MOD(MOD(B3,9.5),1)*60,0)-$F$2,TIME(HOUR(A3),MINUTE(A3),SECOND(A3))+TIME(MOD(B3,9.5),MOD(MOD(B3,9.5),1)*60,0)))

And the result was incorrect. It shows me: 2020-7-9 10:01 but it should be 2020-7-10 10:01.

UPDATE: There are few cases, which had to be good:
a)Start 2020-07-09 8:01 -> Add 1 hour -> result - 2020-07-10 9:01
b)Start 2020-07-09 7:00 -> Add 1 hour -> result - 2020-07-10 9:00
c)Start 2020-07-09 17:31 -> Add 1 hour -> result - 2020-07-10 9:00
d)Start 2020-07-09 00:00 -> Add 1 hour -> result - 2020-07-10 9:00

And also:
a)Start 2020-07-10 8:01 -> Add 1 hour -> result - 2020-07-10 9:01
a)Start 2020-07-10 20:22 -> Add 1 hour -> result - 2020-07-13 9:00

Any help?

UPDATE: https://i.sstatic.net/Lq6bs.png


Solution

  • Adding this as a new answer. It is very specific to the logic requirements of the original question.

    Sample worksheet is here:

    https://docs.google.com/spreadsheets/d/1kmXE6QlqRLFNS9rKNwiqTX-blgJKjj8w0SayJVeQdZo/edit?usp=sharing

    Main formula is as follows:

    =if(or(
        weekday(A6,2)>5,
        iferror(match(INT(A6),$H$1:$H12,0),FALSE)),
          workday(A6,
                  INT(1 + int(B6/9.5) ),
                  Sheet2New!Holidays)
            + (8 + mod(B6,9.5))/24 ,
          workday(A6,
                  INT(0 + int(B6/9.5) + if((if(D6/24<$B$2,8,D6) + mod(B6,9.5))-$D$3>0,1,0)),
                  Sheet2New!Holidays)
    
            + if(round(D6/24,4)<=$B$2,
                8+mod(B6,9.5),
                if(round((D6 + mod(B6,9.5))/24,4)>=$B$3,
                  if(round(D6/24,4)>=$B$3, 
                    8 + mod(B6,9.5),
                    8 + mod(B6,9.5)-($C$3*24-D6)),
                D6 + mod(B6,9.5)))  /24)
    

    It could use a lot of cleaning up, but will leave that until after seeing if it works for you.

    It basically uses WORKDAYS to calculate a future date, based on a starting date/time, and an added number of hours. The first part of the formula focuses on getting the new date right, and the second part focuses on getting the new start time.

    The logic rules include:

    Let me know if it tests out okay.

    UPDATE: revised formula to also check if starting on a weekend. If so, treat the same as if starting on a holiday.