Formulas to Calculate Dates/Duration

rmc0030

I manage an intake process and want to calculate the duration that an item is in each stage (there are 3 stages)

I created 3 helper columns - Stage 1 Date, Stage 2 Date, and Stage 3 Date. I also created automation so that when an item reaches Stage 1, the date is recorded —- same with Stage 2 and 3.

I need help on what formulas I can use to pull in meaningful data. As a starting point, I would like to capture the duration in each stage. Which formula would I use here?

As an extra layer, it would be great to pull in duration of items in each stage based on another factor, such as portfolio type.

Any assistance would be greatly appreciated :)

Kelly Moore

Hey @rmc0030

To find the Stage 1 duration you may need to add the system column Created (date) to your sheet, if you don't already have it. This will allow you to calculate from initiation to Stage 1.

=NETDAYS(Created@row, [Stage 1 Date]@row)

The Stage 2 duration is the elapsed time from Stage 1 until Stage 2

=NETDAYS([Stage 1 Date]@row,[Stage 2 Date]@row)

Stage 3 becomes

=NETDAYS([Stage 2 Date]@row, [Stage 3 Date]@row)

If interested, the overall duration is

=NETDAYS(Created@row, [Stage 3 Date]@row)

Depending upon the layout of your sheet, you might be able to gather average Portfolio type durations using a Grouped report (grouped on Portfolio type).

Will the above work for you?
Kelly

rmc0030

@Kelly Moore Thank you so much - this is incredibly helpful! If I wanted an average of all the outputs above (duration stage 1, duration stage 2, duration stage 3), would I do an AVG formula or what's the best way to pull in the duration end to end?

Kelly Moore

Hey @rmc0030

Yes, the AVG would work for each of your durations, for example:

=AVG([Stage 1 Duration]:[Stage 1 Duration])

If you have an extreme outlier that is skewing the average, you can also use the MEDIAN instead of AVG.

As shown above, the formula for end to end is:

=NETDAYS(Created@row, [Stage 3 Date]@row)

This assumes that Stage 3 Date is the final step that you are able to track

Let me know if you have more questions - I'm happy to clarify anything.

Kelly

Leroy Noriega

This was exactly what I was looking for! Thank you