Apr 05 2021 10:29 AM
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.
Apr 05 2021 10:40 AM
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.
Apr 05 2021 11:25 AM
Try the following suggestion:
--------------------------------------------
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2022)
ISRAEL
Apr 05 2021 08:02 PM
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.