cutiegal886
New Member
- Joined
- Jul 15, 2017
- Messages
- 2
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?
<tbody>
</tbody>
=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
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?
A | B | C | D | E | F | G | |
1 | Start | Hours | End | Begin | End | Holiday | |
2 | 2017-7-15 08:30 | 5 | 8:00 | 5:00 | 2017-7-16 |
<tbody>
</tbody>
=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