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

Find more posts tagged with

Formulas

Accepted answers

All comments

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?

Stefan

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.