Calculation field in pivot table not working

[amylou1]

so, i know this should be really simple, but it is not working for me!! i am beyond frustrated, when i looked at some of the issues on here, i feel really dumb, so i'm really hoping for something quick and easy to solve my problem! fingers crossed!! i just need to be able to see how long it takes for us to get approval of documents. once i get my pivot set up, i will be able to update this as needed, but i'm having issues with the set up! we have a tracking list that we update weekly, so it is rather large, which is why i am doing this. we just want to be able to show how long it takes for submittal to signature. i have my dates, i have inserted my calculations field, i have done one with the DAYS formula and one with just subtracting the submitted date from the signature date...and it is not working!

what am i doing wrong??

thank you!

[#2]

@amylou1 The number -43927 suggests that the calculation doesn't recognize the approval date as such. The calculated field returns the result of 0 minus April 6, 2020 = -43927. Remember that dates are in fact sequential numbers starting at 1 on January 1, 1900. April 6, 2020 is day number 43927.

Can't tell why the May 6 date isn't seen by Excel without looking at the data. Perhaps the approval dates are in fact texts that merely look like dates.

The attached file contains an example that does what you describe. Though I chose the sum function in the pivot table. Not sure why you chose product and count.

[#3]

@Riny_van_Eekelen I have checked to ensure that the date fields were formatted correctly and they were, I also changed to sum. I'm still getting the same results...I'm not sure how to attach the file...so I will do screen shots if that helps you...

thank you!

[#4]

@amylou1 Formatting a range as Date does not transform its content to dates if they are texts to begin with. what do you get when you use =ISNUMBER(cell) where "cell" points to a cell with an approval date?

If TRUE then it probably is a date. If FALSE, then it is not.

Can you otherwise share a link your file on Onedrive, Dropbox or similar?

[#5]

https://fldot-my.sharepoint.com/:x:/g/personal/amy_causseaux_dot_state_fl_us1/EdXoFn9MujFBi9O5PskrfC...

I don't think this will work as it usually blocks from sharing outside of the department

[#6]

also, got a true

[#7 · Accepted solution]

@amylou1 Doesn't allow me to download. try sending it to me via a direct message.