Date Formula for the Monday following a Date

Emilie Nichols · 07/16/21

I'm looking for a formula or automation that calculates the date of the Monday following a specific date. The initial date may be a Tuesday, Wednesday, or Thursday, but the date I need to "fill in" will always be Monday, as it's a due date. In the example below, the Schedules Due Date column needs to be the Monday after the date in the Confirmation Date. Any ideas? Thanks in advance!

Andrew1983 · 07/16/21

Hi all, <\/p>\n

I've seen similar requests on these forums but unfortunately I have not yet been able to make this work. <\/p>\n

I have a project schedule with Start Date and End Date columns. My leadership would like to see two associated columns that show the Monday before<\/em> the Start Date and End Date to help with planning - so they can see everything starting and coming due in a given week. <\/p>\n

Our initial idea was to simply manually enter this but as dates shift over time and the schedule grows, this will get complicated. <\/p>\n

Can someone help with the formula that will essentially do \"If X date, show prior Monday date unless X date is Monday, then just show X date.\" <\/p>","bodyRaw":"

Hi all, <\/p>\n\n

I've seen similar requests on these forums but unfortunately I have not yet been able to make this work. <\/p>\n\n

I have a project schedule with Start Date and End Date columns. My leadership would like to see two associated columns that show the Monday before<\/em> the Start Date and End Date to help with planning - so they can see everything starting and coming due in a given week. <\/p>\n\n

Our initial idea was to simply manually enter this but as dates shift over time and the schedule grows, this will get complicated. <\/p>\n\n

Can someone help with the formula that will essentially do \"If X date, show prior Monday date unless X date is Monday, then just show X date.\" <\/p>","format":"html","dateInserted":"2018-06-05T16:06:11+00:00","insertUser":{"userID":50236,"name":"Matt.Robinson","url":"https:\/\/community.smartsheet.com\/profile\/Matt.Robinson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-10\/c3\/a6\/nc3a69c0268b35e36cdac1ae9e40770a9.jpg","dateLastActive":"2018-10-22T07:31:43+00:00","banned":0,"punished":0,"private":false,"label":""},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/26476\/automatically-populate-monday-before-existing-date-in-sheet","embedType":"quote","name":"Automatically Populate Monday before Existing Date in Sheet"}"> https://community.smartsheet.com/discussion/26476/automatically-populate-monday-before-existing-date-in-sheet

I actually just had the same issue, but I needed the previous monday.

=IF(ISDATE([email protected]),如果(工作日([email protected]) = 1,[email protected]- 6,[email protected]- WEEKDAY([email protected]) + 2))

Make sure your column property is set "DATE" or else you'll get an "invalid column value" error.

I was going to say you need two columns to do this but I see now you're using the "confirmation date" as your actual date and then you will have your calculated Monday date in your "schedules due" date.

Date Formula for the Monday following a Date

Answers

Help Article Resources

Categories