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.

Thanks.

dates and 1st Tuesday of month
 

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.

dates and 1st Tuesday of month
 

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.

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


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

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

dates and 1st Tuesday of month
 

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.

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

dates and 1st Tuesday of month
 

"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

dates and 1st Tuesday of month
 

"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

dates and 1st Tuesday of month
 

"T. Valko" wrote in message
...
"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!

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!

--
Biff
Microsoft Excel MVP

dates and 1st Tuesday of month
 

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" wrote in message
...
"T. Valko" wrote in message
...
"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!

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!

--
Biff
Microsoft Excel MVP

dates and 1st Tuesday of month
 

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


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

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!

--
Biff
Microsoft Excel MVP