View Single Post
  #7   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

One way,

first formula, assume you put it in C27

=C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5)


will give you the first Tuesday's date of the month and year in C26
assuming you have a real date in C26, so if you have 07/01/07 it will
return 07/03/07

then in C28 put

=DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5)


select C28 and copy down as long as needed


You can do this with a single formula if desired. Place this formula

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

in C27 (or any other starting cell for that matter) and copy down. If one
wanted to place this first Tuesdays across columns instead of down rows,
then replace the ROW function calls with COLUMN function calls instead.

Rick