Expected Delivery Date - Specific Delivery Week Days

[Lucy520]

Hi,

We have a customer with several depots, and each has different fixed days of the week they accept deliveries from us. We tell the transport team what date they should plan the delivery for based on the order date (which is not always the date the order is processed) so we manually calculate this.

We aim to deliver the order on the 5th working day (counting the order date), or the next delivery day after that. Taking the example below, since the 5th working day falls on a non-delivery date for depot 101, we will deliver on the next delivery day we have for it (Wed).

Example:

Order Date: 11/06/24

The way we calculate it, the expected delivery dates for the above depots would be:
101: 19/06/24
102: 17/06/24

I thought I finally got it using WORKDAY.INTL by setting the non-delivery dates as weekend days, but then it only counts the delivery days as working days which is not what we want.

I want to be able to edit the order date value and calculate the expected delivery date for each depot based on the selected order date.

I've wasted too much time to figure this out on my own and using ChatGPT because it looks like a simple thing to do, but it's now time I ask for help so I would greatly appreciate any ideas.

Many thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

=WORKDAY.INTL(WORKDAY(A2,4),1,"1101111")

 

[Lucy520]

Thank you for the suggestion, but I believe it's the same problem I mentioned where it only counts a day as a working day if it's also a delivery date, whereas I need it to count 5 working days(Mon-Fri) and if the 5th day falls on a non-delivery day, move forward until the closest delivery day. If the 5th day is a delivery day then that is the expected delivery date.

I changed the formula to the below to match the example for the 101 depot

=WORKDAY.INTL(WORKDAY(A2,4),1,"1100111")

The results are as follows:

Order date	Expected delivery	The results I'm expecting
10/06/2024​	19/06/2024​	19/06/2024​
11/06/2024​	19/06/2024​	19/06/2024​
12/06/2024​	19/06/2024​	19/06/2024​
13/06/2024​	20/06/2024​	19/06/2024​
14/06/2024​	26/06/2024​	20/06/2024​
17/06/2024​	26/06/2024​	26/06/2024​

​

I'm not really sure how to explain this better, probably why I couldn't manage to find the solution online.

Appreciate the help!

 

[Fluff]

If you are counting the start date as one day, then use

=WORKDAY.INTL(WORKDAY(A2,3),1,"1100111")

 

[Lucy520]

Thank you so much, I just worked that out and was coming here to let you know
I'm glad to see the confirmation it's the right solution.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[bobsan42]

Another way to sort it out:

2024-06-11 Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Remarks:	Weekend = Saturday, Sunday				Holidays (if necessary)		Depot	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2		FirstDayOfWeek = Monday				01.06.2024		101			TRUE	TRUE
3						15.06.2024		102	TRUE		TRUE		TRUE
4	order date	11.06.2024				20.06.2024		103				TRUE
5	target delivery date	17.06.2024				01.07.2024		104						TRUE
6	delivery time	5	working days (incl. order date)			16.07.2024
7						31.07.2024
8	deliver to:					15.08.2024
9	101	19.06.2024	Wednesday			30.08.2024
10	102	17.06.2024	Monday			14.09.2024
11	103	20.06.2024	Thursday
12	104	22.06.2024	Saturday
13
Sheet2

Cell Formulas
Range	Formula
C5	C5	=WORKDAY.INTL(C4,C6-1,1,$G$2:$G$10)
C9:C12	C9	=LET(target_date, $C$5, depot_id, $B9, target_weekday, WEEKDAY(target_date,2), depot_days, XLOOKUP(depot_id,$I$2:I$10,J$2:P$10,,0), days_plus, LET( plus,SEQUENCE(1,7,0,1), checkDate, target_date+plus, checkWeekDay, WEEKDAY(checkDate,2), isDeliveryDay,INDEX(depot_days,1,checkWeekDay), output, IF(isDeliveryDay,checkDate,""), output), result, MIN(days_plus), result)
D9:D12	D9	=C9
G6:G10	G6	=G5+15

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:P10	Cell Value	=TRUE	text	NO

this is a bit different, but I had to try it:
this takes into account the actual holidays at first, then it loops to check the available delivery dates, but at this point it doesn't check against the holidays again (this is on behalf of me getting lazy with age or it's the heat )
so you can see in the example date for depot 103 and 104 are wrong - first is on a holiday, second is on a weekend because it has true on Saturday, but weekends are excluded from the target date calculation.