Week Numbers in Excel

re: Week Numbers in Excel

Dan Slaby — Tue, 07 Jul 2009 03:04:44 GMT

Is there an easy way to pick the first Tuesday, etc of every month for the full year?

Ron de Bruin — Tue, 07 Jul 2009 19:51:31 GMT

Hi Dan

with a date in A2 this will say true if it is the first Tuesday of the month

=IF(AND(WEEKDAY(A2,2)=2,DAY(A2)<=7),TRUE,"")

Harlan Grove — Thu, 09 Jul 2009 07:34:31 GMT

Dan, if you mean given any date x you want the first Tuesday of the month in which x falls, try

=x-DAY(x)+7-WEEKDAY(x-DAY(x)-1,3)

x-DAY(x) is the last day of the preceding month. WEEKDAY(y,3) returns 1 when y is a Tuesday, so this generalizes to

=x-DAY(x)+7-WEEKDAY(x-DAY(x)-d,3)

where d is the value WEEKDAY(y,3) would return for the for the desired day of the week, so 0 for Monday, 1 for Tuesday, 2 for Wednesday, etc.