Can Smartsheet automatically push a date on to a Thursday?
Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code/formula for Smartsheet to check whether an End Date (publication date) falls on a Thursday and for Smartsheet to automatically move the date along to the next Thursday if it is not currently landing on a Thurs. For instance, if the pub date from the schedule is naturally falling on Monday 6th (based on its predecessors, which may or may not be drawing in from external sheets' dates), Smartsheet recognises that it’s not a Thurs and automatically pushes the date on by three days to Thurs 9th. Obviously if it naturally falls on a Thursday, I'd like Smartsheet to recognise it and leave it be.
Any help would be much appreciated. Thank you
Best Answers
-
Paul Newcome ✭✭✭✭✭✭Give this a try:
=[Publication Date]@row + (5 - WEEKDAY([Publication Date]@row)) + IF(WEEKDAY([Publication Date]@row) > 5, 7, 0)
Answers
-
Paul Newcome ✭✭✭✭✭✭
Give this a try:
=[Publication Date]@row + (5 - WEEKDAY([Publication Date]@row)) + IF(WEEKDAY([Publication Date]@row) > 5, 7, 0)
-
Lee5 ✭Thanks@Eric Lawand@Paul Newcome. Both worked a treat for me. I can see uses for both formulas also. Paul's answers the question exactly to always push on to a Thurs, and there will also be projects where I wouldn't want to push on by a week if it was only coming in a day later than the Thursday, so Eric's fits that one perfectly.
In order for them to work, I ended up having to create a new column, which I called Publication Date, as the End Date column with dates in needed to feed the formula.
Thanks for helping out so quickly and effectively (I had been stuck on this for months) and for teaching me a thing or two.
Hope you have a great rest of the day.
Cheers
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
=[Column B]@row \/ [Column A]@row <\/p>
<\/p>
EDIT: Then apply the percentage format to the column.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111167,"type":"question","name":"Looking to Countif a person's name is contained in a column that allows for multiple contacts.","excerpt":"=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If a person's name is contained in that column and it matches the name in the column on the metrics sheet called Contact - then I want it to count…","snippet":"=COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row)) the OTP Goals Project Lead is the sheet that has the column that is a contact list that allows for multiple contacts. If…","categoryID":322,"dateInserted":"2023-10-04T02:50:21+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T13:57:49+00:00","insertUserID":131821,"insertUser":{"userID":131821,"name":"Melitta","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Melitta","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7FjR-2zeSSY!zOlQs_Tjxjo!J3yyx3hqT9S","dateLastActive":"2023-10-04T17:39:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-04T17:50:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3392817490,"url":"https:\/\/community.smartsheet.com\/discussion\/111167\/looking-to-countif-a-persons-name-is-contained-in-a-column-that-allows-for-multiple-contacts","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111167\/looking-to-countif-a-persons-name-is-contained-in-a-column-that-allows-for-multiple-contacts","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111167,"commentID":398349,"name":"Re: Looking to Countif a person's name is contained in a column that allows for multiple contacts.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398349#Comment_398349","dateInserted":"2023-10-04T13:57:49+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-10-04T17:50:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T11:48:58+00:00","dateAnswered":"2023-10-04T09:06:57+00:00","acceptedAnswers":[{"commentID":398315,"body":"
Hi @Melitta<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIFS({OTP Goals Project Lead}, CONTAINS(Contact@row,@cell)<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111156,"type":"question","name":"Replace Multiple Values with Multiple Values","excerpt":"I have a Status column which is a dropdown list. I would like a formula that checks the Status column and returns a different number for each item in the list. Any suggestions on how to do this? STATUS. RETURNED VALUE \"NA\". \"1\" \"Not Started\". \"2\" \"Priors Pending\" \"3\" and so forth.....","snippet":"I have a Status column which is a dropdown list. I would like a formula that checks the Status column and returns a different number for each item in the list. Any suggestions on…","categoryID":322,"dateInserted":"2023-10-03T22:03:18+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T15:18:10+00:00","insertUserID":151465,"insertUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-10-04T15:17:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151465,"lastUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-10-04T15:17:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3392804488,"url":"https:\/\/community.smartsheet.com\/discussion\/111156\/replace-multiple-values-with-multiple-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111156\/replace-multiple-values-with-multiple-values","format":"Rich","lastPost":{"discussionID":111156,"commentID":398378,"name":"Re: Replace Multiple Values with Multiple Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398378#Comment_398378","dateInserted":"2023-10-04T15:18:10+00:00","insertUserID":151465,"insertUser":{"userID":151465,"name":"MLK","url":"https:\/\/community.smartsheet.com\/profile\/MLK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zbHV85tf1Hw!XJ1Un0NmDCQ!G7y689MNH3u","dateLastActive":"2023-10-04T15:17:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T15:17:23+00:00","dateAnswered":"2023-10-04T05:59:20+00:00","acceptedAnswers":[{"commentID":398307,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/DN5705XPOATY\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
