How would you measure progress by quarter

Hi there, I am hoping to crowdsource some creative answers to a question that has been asked of me by my leadership team. I currently have a Project Management Office/Organization set up in Smartsheet where projects come in with a request form and are managed on an intake sheet, get their own project planning grid & dashboard and then all roll up to the high-level organizational overview dashboard. Information like on track/off track, % complete and planned % complete are rolled up to the top level.

However, we have many long-term projects, and I am being asked can we automatically calculate the % complete for the given year/quarter. So, for example, if the project is 33% complete and it is a 3-year project I would expect that this year's % complete should be 100%. Conversely, if I have a project starting December 1 2024 and ending January 31st 2025 its a 2 month project equally spanning 2 months. So, if the total task is showing 25%, we are looking to say the % complete for this quarter is 50%.

I am thinking I have to us helper columns with the start and end date of the quarter to then use in calculations to calculate the ratio of the portion of the project in the current quarter to divide the overall task % complete with to get the % complete of this quarter. Does that make sense? Any better ideas?

Find more posts tagged with

Sheets

Formulas

Reports

metrics

Accepted answers

All comments

Jeremy_D

I had a very similar situation and it took me days to come up with a solution. My certain situation had a few extra challenges but this is what I did:

Set up a helper column called 'Spend Type'. This is a simple IF formula which uses the dates of the project to give it a code for when the project starts and finishes. The formula looks something like this: IF(Start@row > DATE(2024, 1,1), Finish@row < DATE(2025, 1,1), “SIY FIY”…
- The code SIY FIY stands for 'Starts In-Year', 'Finishes In-Year'. You'll need 4 codes (SIY FIY, SIY FNY, SPY FIY, SPY FNY) where PY = Previous Year and NY = Next Year

The formula that calculates the %complete is based on the helper column: For SIY FIY, it's easy, the % complete is the % complete.

SPY FIY it's (%complete of total duration - duration outside this year) / duration during this year. See below

SIY FNY it's (%complete of total duration) / duration during this year

SPY FNY you have to use both of the above formulas to get an average.

I hope that makes sense. I have written documentation with the formulas I used, but they add in more parameters that probably make it more confusing.

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.