Help with Time/Date calculation to add hours excluding evening and weekends

[mike435]

Hello,
On a previous thread, the formula below calculates the time difference between two times and dates excluding evenings and weekends:
=SUMPRODUCT(INT((WEEKDAY(B2-{2,3,4,5,6})+INT(H2)-INT(B2))/7),{"16:10","16:10","16:10","16:10","15:40"}-"8:00")+MOD(H2,1)-MOD(B2,1)-IF(WEEKDAY(H2)=6,"15:40","16:10")+"8:00"

Is it possible to have a similar formula that adds 10 hours onto a date and time taking into account working hours. For example you start a job at 14:00 but stop at 17:00 as the factory closes and restarts again at 06:00. The job would then finish at 13:00.

Thanks Mike

 

[barry houdini]

Hi Mike, did you find an answer to this?

This is a solution I posted elsewhere many moons ago......

If you have your start date/time in A1 then hours to add in B1 (in time format e.g. 36:00 for 36 hours) then this formula in C1 will give end date/time

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

where D2 and E2 shows the start and end times of the working day, e.g. 08:00 in D2 and 17:00 in E2

 

[mike435]

Hi Mike, did you find an answer to this?

This is a solution I posted elsewhere many moons ago......

If you have your start date/time in A1 then hours to add in B1 (in time format e.g. 36:00 for 36 hours) then this formula in C1 will give end date/time

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

where D2 and E2 shows the start and end times of the working day, e.g. 08:00 in D2 and 17:00 in E2

Hi,
This works fine when the hours of work are the same.
However, I am trying to find a solution where the times of work vary each day. Monday 8am-4pm, Tue 7am-3pm, Wed 9am-5pm, Thur 7.30am-8pm, Fri 8am-12pm.

Thanks

Mike

 

[barry houdini]

Hi,
This works fine when the hours of work are the same.
However, I am trying to find a solution where the times of work vary each day. Monday 8am-4pm, Tue 7am-3pm, Wed 9am-5pm, Thur 7.30am-8pm, Fri 8am-12pm.

Thanks

Mike

Will the start date/time always be within the working hours? Can Saturday or Sunday be working days?

 

[mike435]

Will the start date/time always be within the working hours? Can Saturday or Sunday be working days?

The start time will always be within working hour. No working on Saturday or Sunday

 

[barry houdini]

OK, I created a small table in G1:L8 as shown below:

You can define the start times and end times for each day (even on Saturdays and Sundays if required) - leave blank if not a working day. Day no. column needs to contain the numbers 1 to 7 as shown.

Helper 1 column has this formula in K2 copied down: =H2+I2
Helper 2 column has this formula in L2 copied down: =H2+J2

Format helper columns any way you want

Then with startdate/time in C2 and working hours to add in D2 (in time format) you can use this formula in E2 to get the end time:

=LET(seq,ROW(INDIRECT("1:"&MAX(8000,D2*1440*6))),m,1440,x,WEEKDAY(seq/m+C2)+MOD(seq/m+C2-1/2880,1),C2+SMALL(IF(COUNTIFS(K$2:K$8,"<"&x,L$2:L$8,">"&x),seq),D2*m)/m)

This will work even if start date/time is outside working hours

Note that I'm using LET function to shorten the formula - LET function should be available in Excel 2021 and later versions

Day	Day no.	Start	End	Helper 1	Helper 2
Sunday	1			1.00	1.00
Monday	2	08:00	16:00	2.33	2.67
Tuesday	3	07:00	15:00	3.29	3.63
Wednesday	4	09:00	17:00	4.38	4.71
Thursday	5	07:30	20:00	5.31	5.83
Friday	6	08:00	12:00	6.33	6.50
Saturday	7			7.00	7.00

 

[RoryA]

This is a solution I posted elsewhere many moons ago......

the last time you were on a forum?

 

[barry houdini]

Hey Rory!

Yeah it's been a long time - they let me out early for good behaviour....

 

[RoryA]

for good behaviour...

I find that very hard to believe...

 

[mike435]

OK, I created a small table in G1:L8 as shown below:

You can define the start times and end times for each day (even on Saturdays and Sundays if required) - leave blank if not a working day. Day no. column needs to contain the numbers 1 to 7 as shown.

Helper 1 column has this formula in K2 copied down: =H2+I2
Helper 2 column has this formula in L2 copied down: =H2+J2

Format helper columns any way you want

Then with startdate/time in C2 and working hours to add in D2 (in time format) you can use this formula in E2 to get the end time:

=LET(seq,ROW(INDIRECT("1:"&MAX(8000,D2*1440*6))),m,1440,x,WEEKDAY(seq/m+C2)+MOD(seq/m+C2-1/2880,1),C2+SMALL(IF(COUNTIFS(K$2:K$8,"<"&x,L$2:L$8,">"&x),seq),D2*m)/m)

This will work even if start date/time is outside working hours

Note that I'm using LET function to shorten the formula - LET function should be available in Excel 2021 and later versions

Day	Day no.	Start	End	Helper 1	Helper 2
Sunday	1			1.00	1.00
Monday	2	08:00	16:00	2.33	2.67
Tuesday	3	07:00	15:00	3.29	3.63
Wednesday	4	09:00	17:00	4.38	4.71
Thursday	5	07:30	20:00	5.31	5.83
Friday	6	08:00	12:00	6.33	6.50
Saturday	7			7.00	7.00

That works perfectly. Thank you for your help