Find date of first Monday of each month

[Ian R]

Hi

I'd like to find out the date of the first Monday of every month for the
current year. Ideally I'd like to display this in a list e.g. A1 to A12

I'm sure this can be calculated in Excel but I'm not sure what formula would
do this.

Any pointers would be gratefully received.

Many thanks

Ian

 

[Niek Otten]

Hi Ian,

Look here:

http://www.cpearson.com/excel/DateTimeWS.htm#LastWeekday

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi
|
| I'd like to find out the date of the first Monday of every month for the
| current year. Ideally I'd like to display this in a list e.g. A1 to A12
|
| I'm sure this can be calculated in Excel but I'm not sure what formula would
| do this.
|
| Any pointers would be gratefully received.
|
| Many thanks
|
| Ian
|
|

 

[Bob Phillips]

=DATE(2008,ROW(),1)+(8-WEEKDAY(DATE(2008,ROW(),1),2))*(WEEKDAY(DATE(2008,ROW(),1),2)>1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Rosenfeld]

Hi

I'd like to find out the date of the first Monday of every month for the
current year. Ideally I'd like to display this in a list e.g. A1 to A12

I'm sure this can be calculated in Excel but I'm not sure what formula would
do this.

Any pointers would be gratefully received.

Many thanks

Ian

A1:
=DATE(YEAR(TODAY()),ROWS($1:1),8)-WEEKDAY(DATE(YEAR(TODAY()),ROWS($1:1),1)+5)

fill down to A12

--ron

 

[Ian R]

=DATE(2008,ROW(),1)+(8-WEEKDAY(DATE(2008,ROW(),1),2))*(WEEKDAY(DATE(2008,ROW(),1),2)>1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Wow!

Thanks Bob

Thats exactly what I needed.

Would have taken me an age to work that out for myself.

I'll study the formula to see how it works.

Thanks again.

Ian I^)

 

[Ian R]

Hi Ian,

Look here:

http://www.cpearson.com/excel/DateTimeWS.htm#LastWeekday

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Thanks for the link Niek.

Looks like Ive got a bit of work to do 8^)

Ian I^)

 

[Rick Rothstein \(MVP - VB\)]

=DATE(2008,ROW(),1)+(8-WEEKDAY(DATE(2008,ROW(),1),2))*(WEEKDAY(DATE(2008,ROW(),1),2)>1)

Wow!

Thanks Bob

Thats exactly what I needed.

Would have taken me an age to work that out for myself.

I'll study the formula to see how it works.

Perhaps worth considering as well ...

=DATE(2008,ROW(),1)+CHOOSE(WEEKDAY(DATE(2008,ROW(),1)),1,0,6,5,4,3,2)

Plus you should be able to figure out how it works relatively easily.

Rick

 

[Bernd P]

Hello,

Select 12 vertical cells and array-enter (enter with CTRL + SHIFT +
ENTER):
=7+DATE(2008,ROW(INDIRECT("1:12")),1)-
MOD(DATE(2008,ROW(INDIRECT("1:12")),1)-2,7)

Regards,
Bernd