Median Aged Time

AnonUser1

I'm trying to calculate the median time a ticket is open for (on a monthly basis, so for issues that were created in January how long were those issues open for?)

Simply adding in the {Created Date}, AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 1) portion leads to "incorrect argument", but otherwise it works, but obviously the number returned would be incorrect.

=MEDIAN(COLLECT({Current Age from Created Date}, {Build Status}, OR(@cell = "Unassigned", @cell = "Scheduled", @cell = "Delivered"), {Created Date}, AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 1), {Priority}, OR(@cell = "Highest - showstoppers")))

Number of Open Issues formula: =COUNTIFS({Created Date}, AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 1), {Priority}, OR(@cell = "High", @cell = "Highest - showstoppers"))

Number of Closed Issues formula: =COUNTIFS({Complete Date}, AND(IFERROR(YEAR(@cell), 0) = 2022, IFERROR(MONTH(@cell), 0) = 1), {Build Status}, OR(@cell = "Complete", @cell = "Canceled"), {Priority}, OR(@cell = "High", @cell = "Highest - showstoppers"))

@Paul Newcome any ideas? You've been a huge help to me in the past. thank you!

smartsheet community example.png

KC Jones

This may be above my level of understanding, but I'll see if I can help. Are you doing this in one sheet or is it a cross reference?

AnonUser1

its based on a cross reference to those columns notated in the curly brackets