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.

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

"Jessica" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Jessica" wrote in message
...
C26 is my start date and I need every first Tuesday/month. Thanks!!

"Jessica" wrote:

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.

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


--
Regards,

Peo Sjoblom



"Jessica" wrote in message
...
C26 is my start date and I need every first Tuesday/month. Thanks!!

"Jessica" wrote:

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.

Thanks so much!

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"Jessica" wrote in message
...
C26 is my start date and I need every first Tuesday/month. Thanks!!

"Jessica" wrote:

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.

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

"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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))

--
Biff
Microsoft Excel MVP


Ooops!

Disregard!

--
Biff
Microsoft Excel MVP

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