Auto calculate first Monday of the month

T

tactps

Well-known Member

Joined

Jan 20, 2004

Messages

3,460

• Mar 31, 2004

• #2

Elongated, but should work:

=DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A1)+1,1)+IF(WEEKDAY(DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A1)+1,1))=1,WEEKDAY(DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A1)+1,1)),IF(WEEKDAY(DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A1)+1,1))>2,9-WEEKDAY(DATE(IF(MONTH(A1)=12,YEAR(A1)+1,YEAR(A1)),MONTH(A1)+1,1))))

I'm sure someone will come out with a better one than this.

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 31, 2004

• #3

Book1
	A	B	C	D
1	3rdWednesday	FirstMonday
2	21/01/2004	2/02/2004
3	18/02/2004	1/03/2004
4	17/03/2004	5/04/2004
5	21/04/2004	3/05/2004
Sheet1

formula:

=DATE(YEAR(EDATE(A2,1)),MONTH(EDATE(A2,1)),CHOOSE(WEEKDAY(DATE(YEAR(EDATE(A2,1)),MONTH(EDATE(A2,1)),1)),2,1,7,6,5,4,3))

...which could no doubt be shortened. edate() requires the analysis tool pack addin to be selected (tools | addins)

 

Upvote 0

T

tactps

Well-known Member

Joined

Jan 20, 2004

Messages

3,460

• Mar 31, 2004

• #4

I was right. Thanks Paddy for better code.

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 31, 2004

• #5

you're welcome, although credit is to Dave McRitchie:

http://www.mvps.org/dmcritchie/excel/datetime.htm

...I just added the edate() bit.

 

Upvote 0

M

Marklarbear

Board Regular

Joined

Nov 6, 2003

Messages

119

Office Version

1. 365

Platform

1. Windows

• Mar 31, 2004

• #6

Thanks for the prompt assistance...

The way that the spreadsheet has been developed, the 3rd wednesdays move across (A4, B4, C4 etc) as do the 1st mondays (A5, B5, C5 etc).

I have attempted to rework the formula to show this... but am a bit lost... Anymore assistance you can offer would be most appreciated.

ray:

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 31, 2004

• #7

Book1
	A	B	C	D
4	21/01/2004	18/02/2004	17/03/2004	21/04/2004
5	2/02/2004	1/03/2004	5/04/2004	3/05/2004
Sheet2

 

Upvote 0

M

Marklarbear

Board Regular

Joined

Nov 6, 2003

Messages

119

Office Version

1. 365

Platform

1. Windows

• Mar 31, 2004

• #8

That works brilliantly..... thatnkyou so much for your assistance..

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 31, 2004

• #9

you're welcome.

 

Upvote 0

B

bosco_yip

Well-known Member

Joined

Dec 2, 2002

Messages

1,947

Office Version

1. 2019

Platform

1. Windows

• Mar 31, 2004

• #10

Try another formula using regular function :

=DATE(YEAR(A1),MONTH(A1)+1,1)+IF(MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-2,7)=0,0,7-MOD(DATE(YEAR(A1),MONTH(A1)+1,1)-2,7))



Regards

 

Upvote 0