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
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:
Working hours are limited to 08:00 to 17:30, or 9.5 hours per day.
If start date is a holiday, jump to the next working day at 08:00, plus offset by the amount of hours specified, keeping a max of 9.5 hours per day, and only working days.
If start time is before 08:00, jump ahead to 08:00, plus hours.
If start time is after 17:30, jump ahead to 08:00 the next working day, plus hours.
If hours added would result in going past 17:30, add remaining hours to 08:00 the next working day.
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.