Help with calculating dates in Pivot tables

[TheMadOctopus]

I'm hoping someone can point me in the right direction. I'm "okay" in excel, but certainly not an expert.

Mission: to create an interactive testing metric dashboard.
I've downloaded a CSV file from the base testing system where test defects are entered. I've created a table for the data, then set up pivot tables and charts that I want to use for an interactive dashboard in excel. I have two dates within the data set, date opened and date closed. I want to show A) the number of days a defect case/ticket has been open IF it isn't closed; and B) show the number of days between when a case/ticket was opened and closed; and C) show the average number of days between opening and closing cases/tickets.

I'm having issues formatting the dates properly and getting the formulas to work properly. I'm also struggling on whether or not to add the formulas to the table, since it's refreshed daily or to do the calculations within the pivot.

Sample table and pivots attached.

Any help is mucho appreciated.

Thank you.

[JeteMc]

Hello TheMadOctopus and Welcome to Excel Forum.
A column (Days Open) has been added to the NCCTesting table using: =IF(G2="","",IF(H2="",TODAY()-G2+1,H2-G2+1))
Three pivot tables are placed on Sheet1 utilizing the new column.
The pivot table on the left displays the number of days a defect case/ticket has been open IF it isn't closed (note the filter is set to Date Closed is blank)
The pivot table in the middle displays the number of days between when a case/ticket was opened and closed (note the filter is set to all Dates Closed except those that are blank)
The pivot table on the right displays show the average number of days between opening and closing cases/tickets (note the filter is set to all Dates Closed except those that are blank)
Let us know if you have any questions.

[TheMadOctopus]

thank you so much! this is exactly what I needed, appreciate it!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.