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

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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.

Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

• ✭✭
Options

• ✭✭✭✭✭✭
edited 04/29/24
Options

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)))))))`

Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!