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.

Order Date	Order Time	Fulfil Date	Fulfil Time
Thursday 14th Dec	10:41	Thursday 14th Dec	17:00
Thursday 14th Dec	13:01	Friday 15th Dec	12:00
Friday 15th Dec	14:29	Monday 18th Dec	13:00

<tbody>
</tbody>

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:

Start		Duration	Finish			Day Start	Day Finish
do 14dec17 10:41:00	do 14dec17 11:00:00	06:00:00	do 14dec17 17:00:00			09:00	17:00
do 14dec17 13:01:00	do 14dec17 14:00:00	06:00:00	vr 15dec17 12:00:00
vr 15dec17 14:29:00	vr 15dec17 15:00:00	06:00:00	ma 18dec17 13:00:00
za 16dec17 14:10:00	za 16dec17 15:00:00	06:00:00	ma 18dec17 15:00:00

<tbody>
</tbody>

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