In a list of Wednesdays which is the next one?

[skinnea]

I have a list of Wednesday dates in column A. I want to conditionally format the list such that the next upcoming Wednesday is highlighted up until we are past it (ie the day after).

I’ve tried a bunch of IF AND NOT TODAY() formulas and had some success at identifying bits of the overall outcome (is today before or after each date), but can’t get a single formula to do the whole thing.

Can anyone put me out of my misery…?

Many thanks in advance!

 

[Fluff]

How about

Fluff.xlsm
	G
1
2	05/10/2022
3	12/10/2022
4	19/10/2022
5	26/10/2022
6	02/11/2022
7	09/11/2022
8	16/11/2022
9	23/11/2022
10	30/11/2022
11	07/12/2022
12	14/12/2022
13	21/12/2022
14	28/12/2022
15	04/01/2023
16	11/01/2023
Data

Cell Formulas
Range	Formula
G3:G16	G3	=G2+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G16	Expression	=G2=WORKDAY.INTL(TODAY(),1,"1101111")	text	NO

 

[etaf]

ignore answered

 

[skinnea]

How about

Fluff.xlsm
	G
1
2	05/10/2022
3	12/10/2022
4	19/10/2022
5	26/10/2022
6	02/11/2022
7	09/11/2022
8	16/11/2022
9	23/11/2022
10	30/11/2022
11	07/12/2022
12	14/12/2022
13	21/12/2022
14	28/12/2022
15	04/01/2023
16	11/01/2023
Data

Cell Formulas
Range	Formula
G3:G16	G3	=G2+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G16	Expression	=G2=WORKDAY.INTL(TODAY(),1,"1101111")	text	NO

That certainly looks to have done it… I’ll confirm next Thu

Would you be able to explain what it’s doing please…?

 

[Fluff]

The string "1101111" represents days of the week running from Mon to Sun. The 1 signifies a weekend & the 0 a workday, so it will return the next Wed from today.

 

[skinnea]

Aah thank you.

Up to yesterday, the list highlighted this coming Wednesday.

Today ( which is now that Wednesday) the list highlights next Wednesday.
Is there a way to only do that after we’ve passed this Wednesday?

So I want it to highlight the next Wednesday on the list until we’ve reached and passed it.

(If it helps, it’s to show who’s chairing a meeting each Wednesday afternoon, hence the need for it to not change till Thursday

 

[Fluff]

Just subtract 1 from the date like

=G2=WORKDAY.INTL(TODAY()-1,1,"1101111")

 

[skinnea]

That nailed it thank you!

 

[Fluff]

You're welcome & thanks for the feedback.