If And Or—What To Use When

[MrDB4Excel]

I have a sheet showing order dates whereby the “Order Delivery Date” needs to show correctly per the following parameters.

If the day of the week shown in column “L” is Saturday, Sunday, or Monday then the date appearing in column “B” needs to be 5 days hence.

As you can see by the attached Xl2bb Mini Sheet, my formulas only have one condition, namely that L5 = “Saturday” but it gets even more confusing based on the “Day of the Week” number shown in column “I” because in rows 11 to 18 column “I” includes only numbers and as you can see the dates showing in column “B” get to be different than intended in 6 of the 7 instances.

Perhaps there are one or more columns not needed.

Any help will be much appreciated.

Bogus OrderPickDate.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Order Delivery Date	Pick Order Date	Date	Time			Actual Pick Order Date	Day of the Week
3
4
5		January 09, 2023	Jan/05/2023 10:22:00	January 05, 2023	10:22:00			January 05, 2023	4		January 09, 2023	Monday
6		February 13, 2023	Feb/08/2023 14:23:00	February 08, 2023	14:23:00			February 08, 2023	3		February 11, 2023	Saturday
7		March 08, 2023	Mar/03/2023 14:55:00	March 03, 2023	14:55:00			March 03, 2023	5		March 08, 2023	Wednesday
8		April 10, 2023	Apr/05/2023 00:00:00	April 05, 2023				April 05, 2023	3		April 08, 2023	Saturday
9
10
11		Day of the Week Number Testing Such That the Numbers shown in I12:I18 are Actual Numbers
12		January 06, 2023	Jan/05/2023 10:22:00	January 05, 2023	10:22:00			January 05, 2023	1		January 06, 2023	Friday
13		February 10, 2023	Feb/08/2023 14:23:00	February 08, 2023	14:23:00			February 08, 2023	2		February 10, 2023	Friday
14		March 06, 2023	Mar/03/2023 14:55:00	March 03, 2023	14:55:00			March 03, 2023	3		March 06, 2023	Monday
15		April 09, 2023	Apr/05/2023 00:00:00	April 05, 2023				April 05, 2023	4		April 09, 2023	Sunday
16		January 10, 2023	Jan/05/2023 10:22:00	January 05, 2023	10:22:00			January 05, 2023	5		January 10, 2023	Tuesday
17		February 14, 2023	Feb/08/2023 14:23:00	February 08, 2023	14:23:00			February 08, 2023	6		February 14, 2023	Tuesday
18		March 10, 2023	Mar/03/2023 14:55:00	March 03, 2023	14:55:00			March 03, 2023	7		March 10, 2023	Friday
Orders

Cell Formulas
Range	Formula
B12:B18,B5:B8	B5	=IF(L5="Saturday",H5+5,K5)
C12:C18,C5:C8	C5	=TEXT(D5,"mmm/dd/yyyy ")&TEXT(E5,"hh:mm:ss")
D12:D18,D5:D8	D5	=H5
K12:K18,K5:K8	K5	=SUM($H5+$I5)
L12:L18,L5:L8	L5	=TEXT(K5,"dddd")
I5:I8	I5	=WEEKDAY(H5,2)

 

[awoohaw]

If you are trying to skip non workdays (weekends for example) you can use the WorkDay.INTL function. Not sure if it is avaliable in Excl 2013, though.

 

[awoohaw]

something like what is in column A below:

mr excel questions 12.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2		Order Delivery Date	Pick Order Date	Date	Time			Actual Pick Order Date	Day of the Week
3
4
5	Fri 2023-01-13	Mon 2023-01-09 00:00	Jan/05/2023 10:22:00	Thu 2023-01-05 00:00	10:22:00			Thu 2023-01-05 00:00	4		Mon 2023-01-09 00:00	Monday
6	Thu 2023-02-16	Mon 2023-02-13 00:00	Feb/08/2023 14:23:00	Wed 2023-02-08 00:00	14:23:00			Wed 2023-02-08 00:00	3		Sat 2023-02-11 00:00	Saturday
7	Tue 2023-03-14	Wed 2023-03-08 00:00	Mar/03/2023 14:55:00	Fri 2023-03-03 00:00	14:55:00			Fri 2023-03-03 00:00	5		Wed 2023-03-08 00:00	Wednesday
8	Thu 2023-04-13	Mon 2023-04-10 00:00	Apr/05/2023 00:00:00	Wed 2023-04-05 00:00				Wed 2023-04-05 00:00	3		Sat 2023-04-08 00:00	Saturday
9
10
11		Day of the Week Number Testing Such That the Numbers shown in I12:I18 are Actual Numbers
12	Fri 2023-01-13	Fri 2023-01-06 00:00	Jan/05/2023 10:22:00	Thu 2023-01-05 00:00	10:22:00			Thu 2023-01-05 00:00	1		Fri 2023-01-06 00:00	Friday
13	Thu 2023-02-16	Fri 2023-02-10 00:00	Feb/08/2023 14:23:00	Wed 2023-02-08 00:00	14:23:00			Wed 2023-02-08 00:00	2		Fri 2023-02-10 00:00	Friday
14	Tue 2023-03-14	Mon 2023-03-06 00:00	Mar/03/2023 14:55:00	Fri 2023-03-03 00:00	14:55:00			Fri 2023-03-03 00:00	3		Mon 2023-03-06 00:00	Monday
15	Thu 2023-04-13	Sun 2023-04-09 00:00	Apr/05/2023 00:00:00	Wed 2023-04-05 00:00				Wed 2023-04-05 00:00	4		Sun 2023-04-09 00:00	Sunday
16	Fri 2023-01-13	Tue 2023-01-10 00:00	Jan/05/2023 10:22:00	Thu 2023-01-05 00:00	10:22:00			Thu 2023-01-05 00:00	5		Tue 2023-01-10 00:00	Tuesday
17	Thu 2023-02-16	Tue 2023-02-14 00:00	Feb/08/2023 14:23:00	Wed 2023-02-08 00:00	14:23:00			Wed 2023-02-08 00:00	6		Tue 2023-02-14 00:00	Tuesday
18	Tue 2023-03-14	Fri 2023-03-10 00:00	Mar/03/2023 14:55:00	Fri 2023-03-03 00:00	14:55:00			Fri 2023-03-03 00:00	7		Fri 2023-03-10 00:00	Friday
Sheet26

Cell Formulas
Range	Formula
A5:A8,A12:A18	A5	=WORKDAY.INTL(H5,5,"1000011")
B5:B8,B12:B18	B5	=IF(L5="Saturday",H5+5,K5)
C5:C8,C12:C18	C5	=TEXT(D5,"mmm/dd/yyyy ")&TEXT(E5,"hh:mm:ss")
D5:D8,D12:D18	D5	=H5
K5:K8,K12:K18	K5	=SUM($H5+$I5)
L5:L8,L12:L18	L5	=TEXT(K5,"dddd")
I5:I8	I5	=WEEKDAY(H5,2)

 

[MrDB4Excel]

This is heading in a better direction but something is still amiss. I changed the value from 5 down to 2. This fixed the first 3 dates but notice the 4th date should be April 8 but turned into April 7.
So not sure why but using the number 5 in the formula gets it way off.

Bogus OrderPickDate.xlsx
	C	D	E	F	G	H	I	J
2	Order Delivery Date	Pick Order Date	Date	Time			Actual Pick Order Date	Day of the Week
3
4
5	January 10, 2023	Jan/05/2023 10:22:00	January 05, 2023	10:22:00			January 05, 2023	4
6	February 10, 2023	Feb/08/2023 14:23:00	February 08, 2023	14:23:00			February 08, 2023	3
7	March 08, 2023	Mar/03/2023 14:55:00	March 03, 2023	14:55:00			March 03, 2023	5
8	April 07, 2023	Apr/05/2023 00:00:00	April 05, 2023				April 05, 2023	3
Orders

Cell Formulas
Range	Formula
C5:C8	C5	=WORKDAY.INTL(I5,2,"1000011")
D5:D8	D5	=TEXT(E5,"mmm/dd/yyyy ")&TEXT(F5,"hh:mm:ss")
E5:E8	E5	=I5
J5:J8	J5	=WEEKDAY(I5,2)

 

[awoohaw]

Do the actual number of workdays for the delivery vary depending on the day of the week? The formula I gave advanced 5 workdays, skipping over Sat, Sun, Mon.
What is amiss?. I cannot tell. And for ease of me understanding can you add the weekday to the formatting of the cells that are being calcuated, as I have done.

 

[awoohaw]

you wrote:

"If the day of the week shown in column “L” is Saturday, Sunday, or Monday then the date appearing in column “B” needs to be 5 days hence."

What is the calcuation for column B if L has Tues, Wed, Thur, or Fri?
if 2 days then use this:

=WORKDAY.INTL(H5,2,"1000011")

 

[Fluff]

Is this what you want?

=IF(WEEKDAY(K5,12)>4,H5+5,K5)

 

[MrDB4Excel]

Okay, I think I see an issue with why this isn’t working well. The number of days between the actual order date and the delivery date is always 3 working days.
So if an order is placed on Wednesday, Thursday, or Friday then it follows that an order placed on Wednesday would not be delivered until the following Monday, an order placed on Thursday would not be delivered until the following Tuesday and an order placed on Friday would not be delivered until the following Wednesday.
Saturdays and Sundays are always omitted in the 3-day count as they are not working days for the company the orders are placed with.
Three examples follow:
Order placed on Wednesday:
Thursday is one working day, Friday is the second working day, and Monday is the third working day and is also the day of delivery, always 3 days.
Order placed on Thursday:
Friday is one working day, Monday is the second working day, and Tuesday is the third working day and is also the day of delivery, always 3 days.
Order placed on Friday:
Monday is the first working day, Tuesday is the second working day, and Wednesday is the third working day and is also the day of delivery, always 3 days.

Bogus OrderPickDate.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
2	Actual Delivery Dates	Order Delivery Date Per workday.intl formula	Pick Order Date	Date	Time			Actual Pick Order Date	Day of the Week
3
4
5	January 10, 2023	January 10, 2023	Jan/05/2023 10:22:00	January 05, 2023	10:22:00			January 05, 2023	4		January 09, 2023	Monday
6	February 13, 2023	February 10, 2023	Feb/08/2023 14:23:00	February 08, 2023	14:23:00			February 08, 2023	3		February 11, 2023	Saturday
7	March 08, 2023	March 08, 2023	Mar/03/2023 14:55:00	March 03, 2023	14:55:00			March 03, 2023	5		March 08, 2023	Wednesday
8	April 10, 2023	April 07, 2023	Apr/05/2023 00:00:00	April 05, 2023				April 05, 2023	3		April 08, 2023	Saturday
Orders

Cell Formulas
Range	Formula
C5:C8	C5	=WORKDAY.INTL(I5,2,"1000011")
D5:D8	D5	=TEXT(E5,"mmm/dd/yyyy ")&TEXT(F5,"hh:mm:ss")
E5:E8	E5	=I5
L5:L8	L5	=SUM($I5+$J5)
M5:M8	M5	=TEXT(L5,"dddd")
J5:J8	J5	=WEEKDAY(I5,2)

 

[awoohaw]

so, you should be good to go with:

=WORKDAY.INTL(I5,3,"0000011")

 

[awoohaw]

and if you have holidays to be concerned with, those can be incorporated into a fourth argument.

WORKDAY.INTL function - Microsoft Support

This article describes the formula syntax and usage of the WORKDAY.INTL function, which returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.

support.microsoft.com