Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: 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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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. "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ryguy7272" wrote: 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-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Orrin" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
Excel 2007 PivotTable
Two ways: Edit source data. Dynamic auxiliary table with macro. http://www.mediafire.com/file/ntym1i1n52n/03_29_09.xlsm |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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 "Orrin" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Ashish Mathur" wrote: 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 "Orrin" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
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" wrote in message ... 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Ashish Mathur" wrote: 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 "Orrin" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate # days from today in a Pivot Table
Thanks for the info. Ashish! Juts did a quick Google search; found this:
http://exceltip.com/st/PivotTable_re...Field/746.html This is helpful too: http://www.dailydoseofexcel.com/arch...-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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Ashish Mathur" wrote: 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" wrote in message ... 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Ashish Mathur" wrote: 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 "Orrin" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate # of days to the future from TODAY | Excel Discussion (Misc queries) | |||
Count Days more than 180 days prior to today | Excel Worksheet Functions | |||
Pivot Table - only show dates greater than =today() | Excel Discussion (Misc queries) | |||
CALCULATE NUMBER OF DAYS BETWEEN A DATE AND TODAY | Excel Worksheet Functions | |||
how to calculate a date .. say 34 days from today | Excel Discussion (Misc queries) |