Forum Discussion
Calculation field in pivot table not working
amylou1 Doesn't allow me to download. try sending it to me via a direct message.
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.
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!
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?
- also, got a true
- https://fldot-my.sharepoint.com/:x:/g/personal/amy_causseaux_dot_state_fl_us1/EdXoFn9MujFBi9O5PskrfCEBsOB3KV4kmBXKDza4FwOhVw?e=kLCzBG
I don't think this will work as it usually blocks from sharing outside of the departmentamylou1 Doesn't allow me to download. try sending it to me via a direct message.