Calculate # days from today in a Pivot Table

[Orrin]

I have a date in my pivot table data and I'd like to calculate in a field an
'aging' number of days since today. So, for example, my item has a date of
3/1/09 and today is 3/29/09, I'd like to calculate to 28. This, of course
would change every day the pivot is refreshed.

I've tried using NOW type functions in my pivot table field calculation, but
no luck. Any suggestions?

Thanks,

O
Orrin Wolf

 

[ryguy7272]

Now will give you time and date, then you have to strip out the time, if you
just want the date. So, use =today()

That will give you 28 days from March 1st. Is there somethign else? Maybe
you need to name your range, such as naming the cell that contains 3/1/09,
especially if you will be refreshing your Pivot Table and the cells will
change daily.

Well, just some food for thought. Fiddle around with it for a bit and see
what happens.

HTH,
Ryan---

 

[Orrin]

Thanks. I'm trying to do it in a pivot table, so I'm inserting a calculated
field but the today() function won't work. How can I create a calculated
field to do this so that it's part of my pivot table?

 

[ryguy7272]

It may have something to do with your sheet names. Look at these:
http://www.business-spreadsheets.com/forum.asp?t=36
http://www.mrexcel.com/archive/Errors/21047.html

Or, there is no data on the sheet...........

Look closely. Whatever it is, you will most likely find it before anyone
else does (we can't see what you are seeing).

Good luck,
Ryan--

 

[ryguy7272]

Sorry, disregard that last post. It was intended for someone over in the
Excel Programming area.

Anyway, let's say you see March 1, 2009 in your Pivot Table, say it is Cell
B2, ok. Put this in Cell B1 =TODAY()-B2. Now, right-click Cell B1, Format
Cells > Custom > Type > dd

That's all there is to it. I thought I responded to this post about an hour
ago, but don't see it here now, so disregard any, other, similar posts. Last
time I said something bad about Excel 2007, and how it used to be easy to do
things in Excel 2003, but now, in 2007, NOTHING is easy. I think that post
was eaten by the forum because I bashed Excel 2007. I won't do that anymore!!

Good luck,
Ryan---

 

[Orrin]

Thanks Ryan. I'm sure I'm doing a poor job explaining. I'm trying to create
it as a calculated field IN the pivot table. I can do it outside of the
pivot in another column as you suggest, but if the pivot gets adjusted, it
might overwrite that calculation. I think I should be able to do it as a
calculated field in the pivot table itself, but I can't figure it out. The
Today() function won't work in the calculated field apparently.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Two ways:
Edit source data.
Dynamic auxiliary table with macro.
http://www.mediafire.com/file/ntym1i1n52n/03_29_09.xlsm

 

[Ashish Mathur]

Hi,

You cannot use TODAY() or NOW() inside a pivot table. I guess your only
option is to compute the days in a separate column in the source data and
then drag that column in the row or column area of the pivot table.

I have not tried it but it should work

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[ryguy7272]

IN THE PIVOT TABLE? AFAIK, you can't do calculations inside the Pivot Table.
You can copy/paste special, and have your pivot data, and do calculations on
the data, but that's not a Pivot table any longer, even if it does look like
one. I think you should just go with one of the suggestions you got earlier
today.

You can double-click on the Pivot table and do all sorts of cool things like
sorting, get top 10, or top 10% or and % b/w 1% & 100%, you can get
Percentage of, or running Total...but you can't do what you're talking about
doing... IN THE PIVOT TABLE. Do it above the Pivot Table, or use a 'helper
column' and do the calculation off to the right.

Good luck,
Ryan---

 

[Ashish Mathur]

Hi,

You can write calculated field and calculated item formulas within a pivot
table. Please read up on the same in the Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[ryguy7272]

Thanks for the info. Ashish! Juts did a quick Google search; found this:
http://exceltip.com/st/PivotTable_report_-_Adding_a_Calculated_Field/746.html

This is helpful too
http://www.dailydoseofexcel.com/archives/2005/08/08/calculated-fields-in-pivot-tables/

I think I have seen that done once before! Excel is pretty amazing; it can
do almost anything!!

Well, Orrin, what are you going to do now? You have several options. Hope
this was enlightening. I certainly learned something new!!

Ryan---