We first need to determine if Excel is recognizing the value in the cell as text or a date.
Method #1: Number Format Drop-down Menu
Here’s an easy way to figure it out the data type of the cell using the Number Format Menu:
- Select the cell.
- Click the Number Format drop-down on the Home tab of the ribbon.
- If the preview of each number format has the same value as the cell contents, then it is Text.
- If the preview changes for number format, then the cell is recognized as a Date.
The image below shows what we will see if the value is stored as Text.
And here is an image of the cell value that is recognized as a date.
Checkout my article and video on Dates in Excel to learn more. This is a very important concept that will help you cleanse your data and work with dates.
Method #2: Filter Drop-down Menu
Another way to see the data type is by using the filter drop-down menu.
In the image below you can see the filter list on the left is all text values. The column for the filter list on the right contains dates, and the date values in the filter list are grouped into Year, Month, Day, Minute, Hour, Second.
If you have some text at the bottom of the grouped dates, then those values will need to be converted to dates to use all the grouping features of a pivot table.
