Trying to show the Saturday date of each week, which changes after the date is reached.


Hello! I am trying to show the Saturday date of each week, which should change to the next Saturday after it is reached. Is there a way to do this in one cell with a formula or an automation? Thank you!

Best Answer


  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Remy V,

    This formula should work in a Date column:

    =IF(WEEKDAY(Date@row) = 1, Date@row + 6, IF(WEEKDAY(Date@row) = 2, Date@row + 5, IF(WEEKDAY(Date@row) = 3, Date@row + 4, IF(WEEKDAY(Date@row) = 4, Date@row + 3, IF(WEEKDAY(Date@row) = 5, Date@row + 2, IF(WEEKDAY(Date@row) = 6, Date@row + 1, IF(WEEKDAY(Date@row) = 7, Date@row + 7)))))))

    It currently goes to the next Saturday if the Date in the reference cell is a Saturday. If you want it to work for Today's Date, then just replace all the "Date@row"s with TODAY().

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @Remy V !

    You can use this formula to automatically output the next Saturday of the current week.

    =TODAY() + 7 - WEEKDAY(TODAY())

    You can use this in a pre-existing date column and output the next Saturday after the date listed using:

    =[Date Column]@row + 7 - WEEKDAY([Date Column]@row)

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!