Formula to find last monday (tue, wedn, thu or friday) for a given month

[Michele]

Hi,
I need a formula to calculate the date of the last monday, tuesday,
wednesday, thursday or friday of a given month.
Can't seem to find the answer anywhere.

example:

day: wednesday (or corresponding nr)
month: 3
year: 2004

Result: 31/03/04


Who can help?

Thank you for reading and eventually answering my question

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/DateTimeWS.htm

Frank

 

[Aladin Akyurek]

=INDEX(EOMONTH(A1,0)-{0,1,2,3,4,5,6},MATCH(C1,WEEKDAY(EOMONTH(A1,0)-{0,1,2,3
,4,5,6}),0))

where A1 houses 1-Mar-04 and C1 4 for wednesday.

 

[Daniel.M]

Hi Michele,

DOW: 1 to 7 (Sun to Saturday)

=DATE(YYYY,Mth+1,1)-WEEKDAY(DATE(YYYY,Mth+1,8-DOW))

Example for Last Friday of Feb 2004:
=DATE(2004,3,1)-WEEKDAY(DATE(2004,3,2))

Regards,

Daniel M.

 

[2rrs]

Hi,
I need a formula to calculate the date of the last monday, tuesday,
wednesday, thursday or friday of a given month.
Can't seem to find the answer anywhere.

example:

day: wednesday (or corresponding nr)
month: 3
year: 2004

Result: 31/03/04


Who can help?

Thank you for reading and eventually answering my question

Formula to find any particular last weekday of the month:
=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0)+Num)+1
where Num = {0,1,2,3,4,5,6} for {Sun,Sat,Fri,Thurs,Wed,Tues,Mon},
respectively. The 0 can be omitted for Sunday.
Credit to "rrdonutz" on the MrExcel board

 

[Michele]

Yes, thank you very much!