Set date in month column when another column used to set the date when task was completed
Hi Smartsheet gurus
I have a need where I want to auto update a column corresponding to its month where in another column is used to set the date a task was completed - the need is to sustain the past months date in the month it was completed column - example - see attacehd sheet
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Best Answer
-
I hope you're well and safe!
You could maybe use the Record a date feature.
Something like this.
- Multiple Workflows (one for each month)
- One Workflow with condition paths for each month
Make sense?
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Try this in your May column:
=iferror(if(and(month([date task completed]@row)=5,year([date task completed]@row)=2021),[date task completed]@row,""),"")
Then you'll change the 5 to whatever the month number is for each subsequent column. The iferror at the beginning and ,"" at the end are added to account for scenarios where the date task completed is blank.
Let me know if it works for you!
-
Hi Heather - thanks for the suggestion - this doesn't quite do the trick - it is much more complicated than getting the date to populate when it is may. I need it to sustain the date for may and then add a new date when the same task is completed in June while keeping the date it was completed in May. We use this for billing our customers - we bill once a month - there are thousands of bills going out at different times of the month - we need to know when the bill was sent out - i.e. Date Task Completed - it needs to update the appropriate months column - keep it there and when the next month starts we need to know when that months bill went out and update that month with the date whilst keeping the previous months dates.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
-
I hope you're well and safe!
You could maybe use the Record a date feature.
Something like this.
- Multiple Workflows (one for each month)
- One Workflow with condition paths for each month
Make sense?
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Andree - Hope you are also staying safe and are well!
Awesome suggestion!
That worked! As usual I was over complicating it......So glad SS put that create date functionality in!
To have everyone understand I created a helper column for each month that looked at the task completed cell withing the date range for that month in the column being updated and set it to one if it fell into the range. With the suggestion Andree made I created a workflow to create a date when the helper column set to one and it updated the month column with the date that was being set (which would be the day the task was completed i.e. today()) - so with the 12 monthly columns (Jan-Dec) will each have a created date once the task complete is changed each month. See example attached.
Thanks much and hope you have a fantastic weekend.
bruce
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!