dates and 1st Tuesday of month

[Jessica]

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.

Thanks.

 

[Jessica]

C26 is my start date and I need every first Tuesday/month. Thanks!!

 

[Sandy Mann]

The first Tuesday of the following month must be 28 or 35 days from the
previous month so you can use:

=IF(MONTH(C26+28)=MONTH(C26),C26+35,C26+28)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Jessica]

Thanks so much!

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

A few keystrokes shorter and robust against inserting a new row 1.

=DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5))

 

[T. Valko]

A few keystrokes shorter and robust against inserting a new row 1.

=DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5))

Ooops!

Disregard!

 

[T. Valko]

Ooops!

Disregard!

Eh, I'm having one of those days! Just can't seem to get my brain in gear.

Disregard my disregard. The formula *does* work. In further testing I made a
change and didn't make the reference absolute which threw me for a loop!

 

[Rick Rothstein \(MVP - VB\)]

LOL ... having one of those days, eh Biff?

As for your formula, I think you can shorten it by going back to the ROW
function call I used (instead of your ROWS one)... unless I am missing
something obvious, I am pretty sure that the only insertions that could
possibly hurt the calculated series is an insertion in the middle of the
series of dates itself... and, in thinking about it some more, I am not
entirely sure an internal insertion hurts it either.

Rick

 

[T. Valko]

Yeah, I'm just feeling blah today. No energy or enthusiasm.

Anyhow, if you inserted a new row 1, then:

ROW(A1)

Becomes

ROW(A2)

Just my personal preference to (almost) always "hardcode" rows/columns.

--
Biff
Microsoft Excel MVP

LOL ... having one of those days, eh Biff?

As for your formula, I think you can shorten it by going back to the ROW
function call I used (instead of your ROWS one)... unless I am missing
something obvious, I am pretty sure that the only insertions that could
possibly hurt the calculated series is an insertion in the middle of the
series of dates itself... and, in thinking about it some more, I am not
entirely sure an internal insertion hurts it either.

Rick