Excel keeps trying to read date as a number even after I format cell as Short Date, Pivot table lookup won't work!

[codeliftsleep]

I am having a maddening issue all of a sudden now that I am pulling in data from SQL Server. Apparently casting to a "Date" doesn't work, as Excel doesn't recognize this as a proper date, so I have to cast to a "DateTime". OK no problem, I just run a formula on the datetime to a column called Fixed_Date to obtain the date only which I use in the pivot tables. However, now this is breaking my normally working cell date references that look it up pivot values dynamically for the last 5 days.

I have a cell formatted as a short date, and a pivot table that I need to look up values by date. However, no matter what I try and do, I keep getting a !#REF error because Excel keeps trying to pass in the numeric value to the pivot table.

I've tried using TEXT(B4,"mm/dd/yyyy") in the GETPIVOTTABLE formula and this did work for a short time but then stopped working for some reason. I am a loss. I literally cannot pull the data with the date fro the cell that sets it dynamically, but if I type it in myself, I can get it working using the TEXT Format trick. When the data sits in Excel itself, this has never been an issue, this ALWAYS worked properly. Now that I am pulling it from SQL Server it seems like something doesn't want to work properly and I don't understand why as the date is in the same format its always been.

Please help, it's driving me crazy!

 

[steve the fish]

Reading your title no matter what you format the date to the underlying value is still the date and therefore is a number. However if you use the text function on that date it ceases to be the date and becomes text. Im not entirely sure what else to add. If you need the date to be a number, ie the date, and textual then you need to two values.

 

[codeliftsleep]

Reading your title no matter what you format the date to the underlying value is still the date and therefore is a number. However if you use the text function on that date it ceases to be the date and becomes text. Im not entirely sure what else to add. If you need the date to be a number, ie the date, and textual then you need to two values.

Well, I mean it's worked for several months the way it was by just using the Workday Formula in the cell and then using that cell reference in the Pivot Table lookup. Now it suddenly no longer works, and I can't figure out why. The only change is that the data for the pivot tables is being pulled in from SQL Server. The weird thing is I have pending and resolved cases and don't have this issue with the pending cases even though it is exactly the same lookup being used on the same formatted data from SQL Server

 

[steve the fish]

Id presume the data from SQL is textual which isnt a date. Convert it to a date then pivot it. Excel does have ability to do some coercion to dates if it sees text that looks like a date. From a little testing i just did it doesnt do that in a pivot but will in a workday formula.

 

[codeliftsleep]

Id presume the data from SQL is textual which isnt a date. Convert it to a date then pivot it. Excel does have ability to do some coercion to dates if it sees text that looks like a date. From a little testing i just did it doesnt do that in a pivot but will in a workday formula.

Steve it was, and I already tried both CAST to a DateTime and CONVERT methods, and it didn't fix it, so on the server I actually converted the column to a Datetime column like the Pending Case DataTable was.
Specifically let me state that the full date time is not the issue...it's when I try to remove the time from it and put it in a Short Date format in another column that is causing the issue.
es I know I could group the pivot table by date but there are lots of date and this doesn't always work properly either in some cases I have seen. Additionally it doesn't show the dates in the proper format when I group, being stuck in the 29-Mar format.

In Excel, I then take this datetime and in a separate column, use the formula INT(D2) and then format the column as a date. I know it's recognized as a date because when I go to sort it gives me Newest to Oldest options. This is the exact same thing now as in the Table in Excel where the pending cases are being housed where he pivots that are using the dynamic date cells work without issue. The resolved case pivots still have the same issue, so I am not sure what else I can do...

 

[steve the fish]

Give us some examples of data that you are trying to convert to date and the default date format of your excel.

 

[codeliftsleep]

Give us some examples of data that you are trying to convert to date and the default date format of your excel.

Steve, I got it working...I'm not sure exactly why it started working all of a sudden, but it did and I haven't had anymore issues with it...