How to calculate delivery date taking account of tea break, lunch break, weekends and holidays?

[cutiegal886]

Hi All,

I'm trying to calculate a production delivery date that account for tea&lunch break, weekends and holidays. I found a useful equation, as shown below, but it doesn't take into account of the tea&lunch breaks in between. Could anyone help me with this?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start[/TD]
[TD]Hours[/TD]
[TD]End[/TD]
[TD][/TD]
[TD]Begin[/TD]
[TD]End[/TD]
[TD]Holiday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017-7-15 08:30[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]8:00[/TD]
[TD]5:00[/TD]
[TD]2017-7-16[/TD]
[/TR]
</tbody>[/TABLE]


=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,1,0),$G$2:$G$2)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))

My tea & lunch breaks are as follows:
10:00~10:15 Morning Tea
12:00~13:00 Lunch
15:00~15:15 Afternoon Tea

 

[James006]

Hi,

You should take a look at this old thread :

https://www.mrexcel.com/forum/excel-questions/526513-find-end-date-time-working-hours-duration.html

Hope this will help

 

[cutiegal886]

Hi,

You should take a look at this old thread :

https://www.mrexcel.com/forum/excel-questions/526513-find-end-date-time-working-hours-duration.html

Hope this will help

Thanks James for your input! I'm not exactly sure how to apply it to my information though.

 

[James006]

Hello,

May be my understanding of your objective is not correct ...

What you are looking for is rather complex ... and the true expert in this field is Barry Houdini ...

May be the following thread is more in line with your objective ...

https://www.mrexcel.com/forum/excel...sh-date-time-lunch-break-bank-holidays-2.html

Hope this will help