Formula: Turn Sprint # into Date or vice versa

I'm attempting to create a formula that will translate Start Date into sprint number. In my spreadsheet there is a column for Sprint that I'd like to populate it with Sprint 1, Sprint 2, Sprint 3, etc based on date.

For example Sprint 1's start date is 12/11/19, Sprint 2's start date is 12/25/19, etc, increasing in two week increments. Right now I fill out out both columns manually, but I'm wondering if there is a way to do this with a formula?

TLDR - I need a formula in my Sprint column that references my Sprint Start Date column (formatted as a date) and increments sprints by 1 (based on two weeks). I'm also open to turning sprint numbers into dates if my approach isn't feasible.

Any help would be appreciated.




  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi Evan,

    not knowing the structure of your sheet I try a quick shot.

    Why not simply add +1 to the value in one row above in the "Sprint" column combined with a IF formula, that checks if the start date in the same row is empty?

    You can set this up once in a cell and drag it down to prepare for coming sprints. Whenever you enter a new date in the "Sprint Start Date" column, the "Sprint" cell will populate.

    Does this help?


    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you provide a screenshot that has data manually entered to show what you are trying to automate? Sensitive/confidential data can be removed, blocked, and/or replaced with "dummy data" as needed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!