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)

 

[MrDB4Excel]

Pulling out my hair, bald by morning. I am trying to work thru this but getting confused.
I'll keep trying your last formula =IF(WEEKDAY(K5,12)>4,H5+5,K5) but just need to plug the right column designations in.

I got it. Your last formula does the trick.
Because I have sent different Xl2bb Mini Sheets with varying columns I had to figure out which columns you meant by columns H & K, thus the next Xl2bb shown here below will show how this gets resolved.
Thank you so much for bearing with me.

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 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	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 10, 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	=IF(WEEKDAY(L5,12)>4,I5+5,L5)
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]

Pulling out my hair, bald by morning. I am trying to work thru this but getting confused.
I'll keep trying your last formula =IF(WEEKDAY(K5,12)>4,H5+5,K5) but just need to plug the right column designations in.

what is it you want to get with this formula? I thought you would be working well with the WORKDAY.INTL function posted earlier.

 

[MrDB4Excel]

See my last reply which I edited to include the resolution and the Xl2bb example.

 

[awoohaw]

Okay, yeah. I go back and edit and folks reply before I can hit send as well.
But I still do not see the workday.intl function in your mini worksheet. (is it in column A?)

But, as you have a solution, that is great. Best wishes!

 

[MrDB4Excel]

Look at column C, and fill colored yellow. That is where the formulas exist,

 

[awoohaw]

Look at column C, and fill colored yellow. That is where the formulas exist,

OK, you must be writing about @Fluff 's formula suggestion then. I didnt offer that solution. my last formula counted 3 workdays past the order date. I'm happy you were able to get a solution.

Best wishes!

 

[MrDB4Excel]

Is this what you want?

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

Hello Fluff, thank you so much, you are awesome. You all are awesome.

 

[Fluff]

Glad we could help & thanks for the feedback.