Help with Conditional formatting

New Contributor

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

@AmyW4y8553 

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.

@AmyW4y8553 

 

Try the following suggestion:

NONAME.png

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

 

@AmyW4y8553 

 

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.