Excel 2007 Pivot tables calculated field returns zero

[dentarthurdent]

I have a pivot table with 2 date fields in it (as rows). I want to add a calculated field that simply tells me the difference (in days) between those 2 dates.

No matter what formula I use to calculate the date difference, the field always returns zero for all rows.
I must be misunderstanding something here but I don't know what!

Example of what I want:

Record # Date1 Date2 Calc field(data area)
001 2011-06-22 2011-06-28 6
etc...

Please help!

 

[BlueHorizon]

Hi,
What formula(s) have you tried? If you post those, we will have a little more information to work with and may be able to help.


Best,
Blue Horizon

 

[SkipVought]

hi,

Difference in days is simply the mathematical difference between two REAL DATES.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dentarthurdent]

I have tried simple arithmetic date subtraction, days360 function and the datedif function. I think it must be that I need to convert date formats to a number first? What confuses me is that any of these methods work fine on the source data.

 

[SkipVought]

If your dates are in a PT Row/Column field, they may be converted to STRINGS.

Why does this have to be done using PT values?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dentarthurdent]

I thought about the dates maybe being in the wrong format so I formatted the cells as dates - still no luck.

As for why it has to be done in the PT, well it doesn't really. I am building this pivot for a bunch of non-techy users who simply want to be able to hit a button to refresh the data and update a chart automatically. The source data comes from a SQL database connection. I thought I could use calculated fields for this but I can simply add a column to the source data to calculate this instead.

Just a pity, as I thought this was exactly the sort of purpose that calculated fields were for?