Calculate Variance for dates with Pivot Table

[Thomas Kuriakose]

Respected Sirs,

We have a table with members receiving jobs and the dates of received, dates of start of job and dates of issue of finished job are recorded.

I created a pivot to get the counts, but we need to check the variance in-between the different dates. I created a calculated field, but not able to get values for the date ranges and if there is a blank it should be skipped.

Thank you very much,

with regards,
thomas

 

[chirayu]

To a Pivot table dates in a data field are just random numbers in no specific order. Only way to get accurate values is to use a helper column in your data set & just have the pivot sum the field

 

[Thomas Kuriakose]

Respected Sir,

Correct me if I am wrong.

So we need to add a column for each variance and then average the difference of values.

Thank you very much.

with regards,
thomas

 

[chirayu]

you're looking at number of days between two dates right? then yes you'll need to add a formula for each variance in separate columns then use the pivot to show that data.

 

[Thomas Kuriakose]

Respected Sir,

Thank you for this information.

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

I inserted an extra column for getting the number of days between two dates and summarized with pivot table.

I would like to know whether we can calculate average or get the number of days excluding zeros and blanks in the pivot table to arrive at the actual number of days.

Thank you very much,

with regards,
thomas

 

[chirayu]

I just made a formula version, easier than pivot

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the formula version.

Thanks,

with regards,
thomas

 

[chirayu]

If you're taking a pure count for the dates then just change your formula to a simple count, I added the <>0 & <>"" as I wasn't sure