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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!