Can Smartsheet automatically push a date on to a Thursday?

Lee5

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

Eric Law

@Lee5 So I can make it so it falls on the current week Thursday, so if it is a Friday it will slide back 1 day.

The formula is =[End Date]@row - WEEKDAY([End Date]@row) + 5

Paul Newcome

Give this a try:

=[Publication Date]@row + (5 - WEEKDAY([Publication Date]@row)) + IF(WEEKDAY([Publication Date]@row) > 5, 7, 0)

Eric Law

@Lee5 So I can make it so it falls on the current week Thursday, so if it is a Friday it will slide back 1 day.

The formula is =[End Date]@row - WEEKDAY([End Date]@row) + 5

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 Law and @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

Happy to help. 👍️