Date question

[Bri]

Hi

I have a workbook with a worksheet for each month of the year. In cells
B1,B2,...B5, I need to put the dates if the 1st Tuesday, the 2nd
Tuesday, etc.
For January 2006, the entries would be January 3 , January 10 ... up to
January 31. February would show only 4 entries.

What formula would do this?

Thanks
Bri

 

[Peo Sjoblom]

Just put the first Tuesday's date in one of the cells then just use

=B1 + 7

if that's too uncomplicated

=A1+7*N-WEEKDAY(A1+7-DOW)

where A1 holds the first date of a month, N is the number of the day and DOW
stands for
Date Of the Week where Sunday would be 1, so if you put 01/01/06 in A1, and
wants the second Tuesday of January

=A1+7*2-WEEKDAY(A1+7-2)

will return 01/10/06

Kudos to Daniel Maher for this ingenious formula

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Ron Rosenfeld]

Hi

I have a workbook with a worksheet for each month of the year. In cells
B1,B2,...B5, I need to put the dates if the 1st Tuesday, the 2nd
Tuesday, etc.
For January 2006, the entries would be January 3 , January 10 ... up to
January 31. February would show only 4 entries.

What formula would do this?

Thanks
Bri

Enter the first date of the month in some cell, e.g. in A1.

Then:

B1: =A1+7-WEEKDAY(A1+4)
B2: =IF(MONTH(B1+7)=MONTH($A$1),B1+7,"")

copy/drag down to B5


--ron