View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default dates and 1st Tuesday of month

How can I get the cells to populate with the dates of the 1st Tuesday
of the month?

I tried =C26+7+7*(MONTH(C26+7)=MONTH(C26)) but that gives me every
Tuesday.


Assuming C26 contains a date within the year of interest, the following
formula gives the date of the first Tuesday in January of that year. Copy it
across through the next 11 columns to get the following first Tuesday of the
next eleven months. If you want to copy it down the rows instead of across
the columns, change both occurrences of the function name COLUMN to the
function name ROW...

=DATE(YEAR($C$26),COLUMN(A1),1+MOD(10-WEEKDAY(DATE(YEAR($C$26),COLUMN(A1),1)),7))

Obviously, to produce the dates for the first Tuesday in January, we only
needed the year of interest... so, if you wanted to change the contents of
C26 from a date within the year to just the year, you could do that... just
change the two occurences of YEAR($C$26) to simply $C$26.

Rick