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.
Cheers
Answers
-
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?
Stefan
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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
Categories
Check out the Formula Handbook template!