Formula for quarter based on finish date

Paul Newcome

@Lchicklis I would suggest starting with @L@123's suggestion for populating the quarter. It is nice and short and should get the job done for you.

Regarding the monthly metrics... You shouldn't have to pull the month into a separate column to be calculated. You should be able to work the MONTH function into your metrics formulas like so (example for October)...

=COUNTIFS({Date Range}, IFERROR(MONTH(@cell), 0) = 10)

Nick Stamatakis

Hi @Paul Newcome @JeremiahHorstick

I've got a mod to this formula I need some assistance with:

I am calculating the average age of projects based on specific SKUs we have (and a cumulative one for all projects). Right now, I am comfortable with how we calculate the average age solely based on the year it was completed in.

This is the formula I use for all projects:

=IFERROR(SUMIFS({PS_Age (Completed)}, {PS_Age (Completed)}, <>"", {Project Status}, "Complete", {PS - End Date}, IFERROR(YEAR(@cell), 0) = 2024) / COUNTIFS({PS_Age (Completed)}, <>"", {Project Status}, "Complete", {PS - End Date}, IFERROR(YEAR(@cell), 0) = 2024), "")

This one is SKU specific:

=IFERROR(SUMIFS({PS_Age (Completed)}, {PS_Age (Completed)}, <>"", {Project Category}, CONTAINS([SKU]$1, @cell), {PS - End Date}, IFERROR(YEAR(@cell), 0) = 2024) / COUNTIFS({PS_Age (Completed)}, <>"", {Project Category}, CONTAINS([SKU]$1, @cell), {PS - End Date}, IFERROR(YEAR(@cell), 0) = 2024), "")

I want to be able to modify my formulas such that I am able to calculate the average age of all projects (first formula) and the average age of SKUs (second formula) for each quarter (Jan to Mar, Apr to Jun, Jul to Sep, Oct to Dec) for year going forward.

I figure I need to modify the tail end of my formulas to add MONTH in there with a BETWEEN but I can't get it to work properly.

Any suggestions? Many thanks in advance!

BONUS: Is it possible to develop a formula to calculate the average age of these things on a rolling quarter basis? My brain hurt when I tried to figure this one out...

Paul Newcome

@Nick Stamatakis To incorporate quarters, you would do something like this:

.....{Date Range}, AND(IFERROR(YEAR(@cell), 0) = 2024, IFERROR(MONTH(@cell), 0) >= 1, IFERROR(MONTH(@cell), 99)<= 3).....

What exactly do you mean by on a rolling quarter basis? Do you mean the current quarter based on today's date?

Nick Stamatakis

Thanks @Paul Newcome . I will toss that in that formula and see how that works relative to my attempt.

Re: Rolling quarter basis: What I mean is that I am taking the average of age of completed projects by looking at a moving 3 month period. For example, if it were April 01 today, I'd want to have the average age of completed projects for Jan 01 to Mar 30. Once it was May 15 (for example), I'd like to calculate the rolling average of completed projects from Feb 15 to April 15.

Might be a task to conquer but not a deal breaker. Curious to know if this is possible.

Thank you in advance sir!

Paul Newcome

@Nick Stamatakis Ok. Understood on the rolling quarter. It is more like a rolling 3 months from today kind of thing. It is possible. Your range/criteria set would be

.....{Date Range}, AND(@cell<= TODAY(), @cell>= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, DAY(TODAY())), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, DAY(TODAY())))), ......................