| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE FORMAT IN PIVOT TABLE FROM EXTERNAL DATA SOURCE
I have created a pivot table directly from an external data source (ODBC
database). I would like to group the results by year/quarter/etc., however the Pivot Table does not seem to be recognising the date field as a date format. The field is displayed as "20070222" but I cannot convert it to a date, and I have not figured out a way to use the calculated field either. Any suggestions? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE FORMAT IN PIVOT TABLE FROM EXTERNAL DATA SOURCE
You need to go back to your ODBC Query itself and add in a field that is an
actual date. The field that you have is just an ordinary number. If your source data does not have a date field (of data type date) in it then you will have to do a bit of SQL manipulation to coerce the number that you have into a date. How you go about coercing the number to date will depend on the source database... -- HTH... Jim Thomlinson "uncreative" wrote: I have created a pivot table directly from an external data source (ODBC database). I would like to group the results by year/quarter/etc., however the Pivot Table does not seem to be recognising the date field as a date format. The field is displayed as "20070222" but I cannot convert it to a date, and I have not figured out a way to use the calculated field either. Any suggestions? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DATE FORMAT IN PIVOT TABLE FROM EXTERNAL DATA SOURCE
Thanks - I have decided to perform a separate query in Excel, make a
spreadsheet & manipulate the date there before making the pivot table. "Jim Thomlinson" wrote: You need to go back to your ODBC Query itself and add in a field that is an actual date. The field that you have is just an ordinary number. If your source data does not have a date field (of data type date) in it then you will have to do a bit of SQL manipulation to coerce the number that you have into a date. How you go about coercing the number to date will depend on the source database... -- HTH... Jim Thomlinson "uncreative" wrote: I have created a pivot table directly from an external data source (ODBC database). I would like to group the results by year/quarter/etc., however the Pivot Table does not seem to be recognising the date field as a date format. The field is displayed as "20070222" but I cannot convert it to a date, and I have not figured out a way to use the calculated field either. Any suggestions? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table Data Source external excel file | Excel Discussion (Misc queries) | |||
| Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
| Pivot table format changes if value of source data is null | Excel Discussion (Misc queries) | |||
| Determine the location of an external source of pivot table? | Excel Worksheet Functions | |||
| Pivot and changing External data source | Excel Discussion (Misc queries) | |||
Hybrid Mode
