Average turnaround time in 45 day period

Megan T

Hi all,

I am trying to calculate the average turn around time in a sheet summary (=ROUND(AVG([Turnaround Time]:[Turnaround Time]))) for the past 45 days (not all time, like it is currently). I tried adding <=TODAY (-45), but that did not return an accurate count. I feel like I'm missing something simple!

Turnaround time is calculated in the sheet as: =IF(ISBLANK([Date Lab Results RCVD]), "", NETDAYS([Date Lab Rcvd], [Date Lab Results RCVD]))

Ramzi K

@Megan T

Try this formula:

=ROUND(AVG(COLLECT([Turnaround Time]:[Turnaround Time], [Date Lab Results RCVD]:[Date Lab Results RCVD], <(TODAY() + 45))))

This assumes that the reference date you want to use is [Date Lab Results RCVD]

I hope this helps,

Cheers,

Ramzi

Ramzi K

@Megan T

Try this formula:

=ROUND(AVG(COLLECT([Turnaround Time]:[Turnaround Time], [Date Lab Results RCVD]:[Date Lab Results RCVD], <(TODAY() + 45))))

This assumes that the reference date you want to use is [Date Lab Results RCVD]

I hope this helps,

Cheers,

Ramzi

Megan T

Like a charm! THANK YOU!

Megan T

@Ramzi K

I noticed this wasn't counting back, but forward. This formula worked for 45 days:

=ROUND(AVG(COLLECT([Turnaround Time]:[Turnaround Time], [Date Lab Results RCVD]:[Date Lab Results RCVD], >=(TODAY() - 45))))

And this one for two weeks:

=ROUND(AVG(COLLECT([Turnaround Time]:[Turnaround Time], [Date Lab Results RCVD]:[Date Lab Results RCVD], >=(TODAY() - 14))))

Thanks again for the assist!