Set date in month column when another column used to set the date when task was completed

Bruce Johnson

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

Example.xlsx

Andrée Starå

Hi @Bruce Johnson

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!

Heather Duff

Hi @Bruce Johnson

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!

Bruce Johnson

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.

Andrée Starå

Hi @Bruce Johnson

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!

Bruce Johnson

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.

Example (1).xlsx

Thanks much and hope you have a fantastic weekend.

bruce