formulas populating dates - 1st Tuesday
 

I have a start date that varies - and I need a formula that populates the
first Tuesday each month. I have tried several different formulas but they
all get messy due to some months being 4 weeks and others 5 weeks. Any
suggestions on which formula to try? Thanks so much!

A1= 1/6/07

Each month needs to be the first Tuesday of the month.

formulas populating dates - 1st Tuesday
 

See if this works :-

On cell A2 type :
=DATE(YEAR(A1),MONTH(A1)+1,1)
and copy it all the way down

Then on cell B2 type this array formula (Ctrl+shift+Enter)
=SUM(IF(WEEKDAY(A2+(ROW(INDIRECT("1:7"))-1))=3,DATE(YEAR(A2),MONTH(A2),(ROW(INDIRECT("1:7") )))))
and copy it all the way down



"Jessica" wrote:

I have a start date that varies - and I need a formula that populates the
first Tuesday each month. I have tried several different formulas but they
all get messy due to some months being 4 weeks and others 5 weeks. Any
suggestions on which formula to try? Thanks so much!

A1= 1/6/07

Each month needs to be the first Tuesday of the month.

formulas populating dates - 1st Tuesday
 

On cell A2 type :
=DATE(YEAR(A1),MONTH(A1)+1,1)
and copy it all the way down

in B1:
=A1+MOD((10-WEEKDAY(A1)),7)

and copy down

"N Harkawat" wrote:

See if this works :-

On cell A2 type :
=DATE(YEAR(A1),MONTH(A1)+1,1)
and copy it all the way down

Then on cell B2 type this array formula (Ctrl+shift+Enter)
=SUM(IF(WEEKDAY(A2+(ROW(INDIRECT("1:7"))-1))=3,DATE(YEAR(A2),MONTH(A2),(ROW(INDIRECT("1:7") )))))
and copy it all the way down



"Jessica" wrote:

I have a start date that varies - and I need a formula that populates the
first Tuesday each month. I have tried several different formulas but they
all get messy due to some months being 4 weeks and others 5 weeks. Any
suggestions on which formula to try? Thanks so much!

A1= 1/6/07

Each month needs to be the first Tuesday of the month.

formulas populating dates - 1st Tuesday
 

Here are two formulas that accomplish what you want. The first formula works
across columns to make column headings and the second works down rows to
produce row headings.
In this example you put a date in A1. The only important part of this date
is the year. In A2 you put the day of the week you want with 1 = Sunday, 2 =
Monday .... 6 = Saturday. In A3 you put which occurrence in the month you
want 1 = first, 2 = second, .... 5 = fifth. You must be careful with
occurrence to insure the occurrence is valid. For example the only way to
have a 5th Wednesday in February is in a leap year.

With the following formulas, put them in the cell of your choosing, format
that cell, then drag the formula across 11 more columns in the case of the
column formula or down 11 more rows in the case of the row formula.

The column formula is:
=DATE(YEAR($A1),COLUMN(A1),1)+$A2-WEEKDAY(DATE(YEAR($A1),COLUMN(A1),1))+($A3-($A2=WEEKDAY(DATE(YEAR($A1),COLUMN(A1),1))))*7

The row formula is:
=DATE(YEAR(A$1),ROW(A1),1)+A$2-WEEKDAY(DATE(YEAR(A$1),ROW(A1),1))+(A$3-(A$2=WEEKDAY(DATE(YEAR(A$1),ROW(A1),1))))*7


"Jessica" wrote in message
...
I have a start date that varies - and I need a formula that populates the
first Tuesday each month. I have tried several different formulas but they
all get messy due to some months being 4 weeks and others 5 weeks. Any
suggestions on which formula to try? Thanks so much!

A1= 1/6/07

Each month needs to be the first Tuesday of the month.

formulas populating dates - 1st Tuesday
 

Here are two formulas that accomplish what you want. They are general
purpose and are easy to change to other days and other occurrences. In A1
you put any date in the year. The only important part of this date is the
year. In A2 you put the day of the week you want with 1 = Sunday, 2 = Monday
...... 6 = Saturday. In A3 you put the occurrence of the day in the month
with 1 = first, 2 = second .... 5 = fifth. The only thing you must be
careful of is to ensure the occurrence number is valid. For example, the
only way there can be 5 occurrences of any day of the week in February is
when February is a leap year.

You enter the formulas in the cell of your choosing, format the result and
drag the formula across 11 more columns in the case of the column formula or
down 11 more rows in the case of the row formula.

The column formula:

=DATE(YEAR($A1),COLUMN(A1),1)+$A2-WEEKDAY(DATE(YEAR($A1),COLUMN(A1),1))+($A3-($A2=WEEKDAY(DATE(YEAR($A1),COLUMN(A1),1))))*7

The row formula:

=DATE(YEAR(A$1),ROW(A1),1)+A$2-WEEKDAY(DATE(YEAR(A$1),ROW(A1),1))+(A$3-(A$2=WEEKDAY(DATE(YEAR(A$1),ROW(A1),1))))*7

`"Jessica" wrote in message
...
I have a start date that varies - and I need a formula that populates the
first Tuesday each month. I have tried several different formulas but they
all get messy due to some months being 4 weeks and others 5 weeks. Any
suggestions on which formula to try? Thanks so much!

A1= 1/6/07

Each month needs to be the first Tuesday of the month.

formulas populating dates - 1st Tuesday
 

Hello Jessica,

If A1 contains the first of any month then this formula in A2 will give you
the first Tuesday of that month

=A1+7-WEEKDAY(A1-3)

then to get each subsequent first Tuesday use this formula in A3 copied down

=A2+28+7*(DAY(A2+35)<8)

"Dave Thomas" wrote:

Here are two formulas that accomplish what you want. They are general
purpose and are easy to change to other days and other occurrences. In A1
you put any date in the year. The only important part of this date is the
year. In A2 you put the day of the week you want with 1 = Sunday, 2 = Monday
...... 6 = Saturday. In A3 you put the occurrence of the day in the month
with 1 = first, 2 = second .... 5 = fifth. The only thing you must be
careful of is to ensure the occurrence number is valid. For example, the
only way there can be 5 occurrences of any day of the week in February is
when February is a leap year.

You enter the formulas in the cell of your choosing, format the result and
drag the formula across 11 more columns in the case of the column formula or
down 11 more rows in the case of the row formula.

The column formula:

=DATE(YEAR($A1),COLUMN(A1),1)+$A2-WEEKDAY(DATE(YEAR($A1),COLUMN(A1),1))+($A3-($A2=WEEKDAY(DATE(YEAR($A1),COLUMN(A1),1))))*7

The row formula:

=DATE(YEAR(A$1),ROW(A1),1)+A$2-WEEKDAY(DATE(YEAR(A$1),ROW(A1),1))+(A$3-(A$2=WEEKDAY(DATE(YEAR(A$1),ROW(A1),1))))*7

`"Jessica" wrote in message
...
I have a start date that varies - and I need a formula that populates the
first Tuesday each month. I have tried several different formulas but they
all get messy due to some months being 4 weeks and others 5 weeks. Any
suggestions on which formula to try? Thanks so much!

A1= 1/6/07

Each month needs to be the first Tuesday of the month.