Tricky Maths formula > Calculate Due time

[JumboCactuar]

Hi,
The hour range is between store open times (Monday - Friday 09:00 till 17:00)

If an order is placed we have a time of 6 hours to ship out the order.

Example:
An order is recieved Monday at 09:53, it has to be completed by 16:00
09:53 rounds up to 10:00 + 6 hours


Though if an order is placed at 15:30 it has to be fulfilled by 14:00 the next day
15:30 rounds to 16:00
16:00 - 17:00 = 1hour
next day 09:00 - 14:00 = 5 hours

As there is no work on a weekend, orders recieved friday after 11pm have to be fulfiled monday.

[TABLE="width: 100"]
<tbody>[TR]
[TD]Order Date[/TD]
[TD]Order Time[/TD]
[TD]Fulfil Date[/TD]
[TD]Fulfil Time[/TD]
[/TR]
[TR]
[TD]Thursday 14th Dec[/TD]
[TD]10:41[/TD]
[TD]Thursday 14th Dec[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]Thursday 14th Dec[/TD]
[TD]13:01[/TD]
[TD]Friday 15th Dec[/TD]
[TD]12:00[/TD]
[/TR]
[TR]
[TD]Friday 15th Dec[/TD]
[TD]14:29[/TD]
[TD]Monday 18th Dec[/TD]
[TD]13:00[/TD]
[/TR]
</tbody>[/TABLE]

The red is what i need to calculate from the order date/time, i dont know how to go about this

Appreciate any help

 

[AlphaFrog]

Fulfil Date: Holidays is a named range of holiday dates

=WORKDAY(A2+B2+6/24+7/24-1,1,Holidays)


Fulfil Time

=CEILING(B2+IF(B2>=11/24,-2/24,6/24),1/24)

 

[JumboCactuar]

Fulfil Date: Holidays is a named range of holiday dates

=WORKDAY(A2+B2+6/24+7/24-1,1,Holidays)


Fulfil Time

=CEILING(B2+IF(B2>=11/24,-2/24,6/24),1/24)

this is great thankyou

only thing, it doesnt account for orders placed after closing time - e.g 19:45

the 6 hours for these times start 09:00 the following day

 

[AlphaFrog]

this is great thankyou

only thing, it doesnt account for orders placed after closing time - e.g 19:45

the 6 hours for these times start 09:00 the following day

Try this fulfil time

=CEILING(IF(OR(B2<9/24,B2>=17/24),15/24,B2+IF(B2>=11/24,-2/24,6/24)),1/24)

 

[PietBom]

This formula will work for your question:
[TABLE="class: grid, width: 789"]
<tbody>[TR]
[TD]Start[/TD]
[TD][/TD]
[TD]Duration[/TD]
[TD]Finish[/TD]
[TD][/TD]
[TD][/TD]
[TD]Day Start[/TD]
[TD]Day Finish[/TD]
[/TR]
[TR]
[TD]do 14dec17 10:41:00[/TD]
[TD]do 14dec17 11:00:00[/TD]
[TD]06:00:00[/TD]
[TD]do 14dec17 17:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]09:00[/TD]
[TD]17:00[/TD]
[/TR]
[TR]
[TD]do 14dec17 13:01:00[/TD]
[TD]do 14dec17 14:00:00[/TD]
[TD]06:00:00[/TD]
[TD]vr 15dec17 12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]vr 15dec17 14:29:00[/TD]
[TD]vr 15dec17 15:00:00[/TD]
[TD]06:00:00[/TD]
[TD]ma 18dec17 13:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]za 16dec17 14:10:00[/TD]
[TD]za 16dec17 15:00:00[/TD]
[TD]06:00:00[/TD]
[TD]ma 18dec17 15:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
1. In A2 put your Order date & Time
2. In B2 you put this formula:

=INT($A2*24+0.999999)/24-0.000000001

3. In C2 put your duration (06:00:00)
4. In G2 Start time of workday and in H2 End time of workday
5. In D2 put formula

=WORKDAY(WORKDAY(WORKDAY($B2,-1),1),CEILING(($C2+MAX(MIN(1*(WEEKDAY($B2,2)<6)*MOD($B2,1),$H$2),$G$2)-$G$2)/($H$2-$G$2),1)-1)+
MAX(MIN(1*(WEEKDAY($B2,2)<6)*MOD($B2,1),$H$2),$G$2)+$C2-CEILING(MAX(MIN(1*(WEEKDAY($B2,2)<6)
*MOD($B2,1),$H$2),$G$2)+$C2-$G$2,$H$2-$G$2)+$H$2-$G$2