How do I calculate first Monday of month for a number of months

[Excel NewB]

Suppose I have the date January 3, 2005 in A1. How do I auto fill in
cells A2 thru A100 with the date of the first Monday of each month?
I have tried the following two formulas that I have found but they
only seem to calculate the first Monday of the current month.

Formula 1:
=IF(WEEKDAY(A1, 3)>0, 7-WEEKDAY(A1, 3), 0)+A1

Formula 2:
=DATE(YEAR(A1),MONTH(A1),CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),2,1,7,6,5,4,3))

thanks in advance

 

[2rrs]

Add a helper col; say E1:E100
In E1 add your date
In E2 enter: =EDATE(E1,1) and drag down to E100
Use your formula 2 in A2; changing A1 to E1; drag down to A100
that should do it

 

[sandy.mann]

Suppose I have the date January 3, 2005 in A1. How do I auto fill in
cells A2 thru A100 with the date of the first Monday of each month?
I have tried the following two formulas that I have found but they
only seem to calculate the first Monday of the current month.

Formula 1:
=IF(WEEKDAY(A1, 3)>0, 7-WEEKDAY(A1, 3), 0)+A1

Formula 2:
=DATE(YEAR(A1),MONTH(A1),CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),2,1,7,6,5,4,3))

thanks in advance

Hi Excel NewB,

It may be too late for you now but I my ISP has blocked
microsoft.public.excel.worksheetfunctions, possibly because it is an
orphaned Newsgroup no longer supported by Microsoft and I have had
difficulties registering with Google to answer via their website.

With your start date in A1, try:

=(A1+32)-DAY(A1+32)+8-WEEKDAY((A1+32)-DAY(A1+32),2)

and copy down using the fill handle.

HTH

Sandy

 

[John W.]

Since the first Monday of a month will always be either 28 days or 35
days after the first Monday of the previous month, you can enter this
formula into A2 and then just copy down to row 100 or whatever:

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

This assumes that the date in cell A1 is not written out in text. The
cells in Column A may need to be formatted into an appropriate date
format.

John W.