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.Back to top

 

[Jonathan Rynd]

(e-mail address removed) (Michele) wrote in @posting.google.com:

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

day: sunday=1, monday=2, etc.

endofmonth:
=EOMONTH(DATE(year,month,1),0)

lastweekday:
=endofmonth-WEEKDAY(endofmonth)+day-7*(WEEKDAY(endofmonth)<day)

 

[Michele]

Thanks for your answer Jonathan,

This would give me the formula:
=EOMONTH(DATE(2004;3;1);0)-WEEKDAY(EOMONTH(DATE(2004;3;1);0))+1-7*(WEEKDAY(EOMONTH(DATE(2004;3;1);0))<1)

If I only change "day" in "lastweekday" the date remains the same (28
march 2004).

So I got the impression it doesn't work what am I doing wrong?
--------------------------------------------------------

 

[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.

-------------
The answer that helped me was:


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

Thank you to all the people that responded to my question.

--------------------------

 

[Jonathan Rynd]

(e-mail address removed) (Michele) wrote in

If I only change "day" in "lastweekday" the date remains the same (28
march 2004).

Could you provide examples of the two formulas with different days yet
returning the same date?