Using formulas to automate a date

Hello! I have a column that identifies a specific week, e.g., week 1, week 2, etc. I would like to create another column that automates a specific date based on week entry. For instance if Week 1 is entered, the date 1/25/21 is automatically entered in the date column. Thank you for your help!


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Autumn Montegna ,

    I assumed your week number was in column [week] and entered as text, e.g. Week 1. The formula is simpler if your week number is just a number. I also assumed all dates are 2021. Try:

    =MAX(DATE(2021,1,1),DATE(2021,1,1)-WEEKDAY(DATE(2021,1,1))+(VALUE(RIGHT([week]@row, LEN([week]@row)-FIND(" ",[week]@row)))-1)*7+9)

    It should return the Monday date of the week number.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!