# Help with Conditional formatting

Occasional Visitor

# Help with Conditional formatting

I need some guidance on one of my formulas. I am trying to highlight the second Tuesday in a month for a duties worksheet. I have the below formula almost working, but it highlights the first and second Tuesday. How do I get it to highlight only the second Tuesday?

=IF(AND(WEEKDAY(B\$4,2)=2,DAY(B\$4)>14),TRUE,FALSE)

B\$4 is the day of the week. Example Fri, Mon, Tue, etc.

3 Replies

# Re: Help with Conditional formatting

Second Tuesday of the month could be calculate as

``=A1+7+CHOOSE(WEEKDAY(A1,2),1,0,6,5,4,3,2)``

for the date in A1 month.

# Re: Help with Conditional formatting

Try the following suggestion:

--------------------------------------------
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2022)
ISRAEL

# Re: Help with Conditional formatting

Here's another way to calculate the second Tuesday, if you have a recent enough version of Excel to use the  LET function,

=LET(FstDa,DATE(L30,L31,1),FstDa+CHOOSE(WEEKDAY(FstDa),9,8,7,13,12,11,10))

FstDa uses DATE to determine the serial number of the first date of the month

The final formula then uses FstDa + CHOOSE to go from that first day of the month to the serial

number of the second Tuesday of the month, by first using WEEKDAY to determine the day of the

week of that first day of the month. CHOOSE adds 7 if that first day itself is a Tuesday,

and increments that by 1 if its a Monday,  2 for Sunday, then jumps up to 13 if the first day is a Wednesday, then 12 if a Thursday, 11 if a Friday, 10 if a Saturday.