I am trying to get the last Monday of the month from a date using a formula.

gestep

I am working on a project where I want to use a formula to return the date of the last Monday in a month. For example, if I have a cell that says 4/11/2024, I want a formula that will then return 4/29/24 (because that is the final Monday of this month).

Gia Thinh

Hi @gestep ,

I am also curious with your problem and try to resolve it. Here is the answer.

Create 2 new columns with formula as below:

Last date of month :

=IF(MONTH(Date@row) < 12, DATE(YEAR(Date@row), MONTH(Date@row) + 1, 1) - 1, DATE(YEAR(Date@row) + 1, 1, 1) - 1)

Last Monday:

=IF(WEEKDAY([Last date of month]@row) = 1, [Last date of month]@row - 6, IF(WEEKDAY([Last date of month]@row) = 7, [Last date of month]@row - 5, IF(WEEKDAY([Last date of month]@row) = 6, [Last date of month]@row - 4, IF(WEEKDAY([Last date of month]@row) = 5, [Last date of month]@row - 3, IF(WEEKDAY([Last date of month]@row) = 4, [Last date of month]@row - 2, IF(WEEKDAY([Last date of month]@row) = 3, [Last date of month]@row - 1, IF(WEEKDAY([Last date of month]@row) = 2, [Last date of month]@row)))))))

Hope it works for you.

Shwetaic

Can you please help how i can get the last friday of the month

Gia Thinh

A bit modification for the 2nd formula:

Last Friday:

=IF(WEEKDAY([Last date of month]@row) = 1, [Last date of month]@row - 2, IF(WEEKDAY([Last date of month]@row) = 7, [Last date of month]@row - 1, IF(WEEKDAY([Last date of month]@row) = 6, [Last date of month]@row, IF(WEEKDAY([Last date of month]@row) = 5, [Last date of month]@row - 6, IF(WEEKDAY([Last date of month]@row) = 4, [Last date of month]@row - 5, IF(WEEKDAY([Last date of month]@row) = 3, [Last date of month]@row - 4, IF(WEEKDAY([Last date of month]@row) = 2, [Last date of month]@row - 3)))))))