Excel Date format issue for generating pivot tables

Question

I have an excel workbook with a large amount of data. When I convert to a pivot table, one of the rows does not change into "Date" format, but instead remains as "General". in my pivot table. As a result I am unable to generate reports based on just months in my pivot table. Can you please let me know how to resolve this issue ? Thank you

Answer 1

Make sure that all dates are really dates and not just texts that look like dates. Use the ISNUMBER function to check it. Real dates return TRUE. In case you see FALSE, the 'date' is in fact a text. And then the automatic date grouping (year, quarter, month) in pivot tables doesn't work.

Answer 2

Thank you for the response @Riny_van_Eekelen. I really appreciate it. The "ISFUNCTION" returned TRUE for the date value.

The original data before has multiple 'date' rows, only one of them remains as "GENERAL" after converting to a pivot table.

I am not sure why this is case. As a result, I am unable to split this particular date to months and quarters. Please let me know where could be the possible issue.

Answer 3

Sorry, but I perhaps don't understand. Did you check all dates in the source data? Are all dates really dates. If not, the pivot table will not work as desired. Correct all 'dates' that are texts in the data source before you create the pivot table.

Answer 4

Sorry if I am not being clear. Some of the dates with "ISFUNCTION" returned FALSE.

How do I convert these text values to real dates before converting to a pivot table?

Answer 5

Hi,

Share the download link of the Excel file and show the problem/expected result very clearly.