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

Options

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

Tags:

Best Answer

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    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
    Bruce Johnson ✭✭✭✭
    Options

    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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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!

    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.

  • Bruce Johnson
    Bruce Johnson ✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!