Changing cell number format not working as expected

Question

I received a large spreadsheet with many columns and rows of data along with columns of dates. The format for all the cells is General. When trying to use a pivot table to calculate difference between two columns of dates, I get the error message that the function I am selecting does not work with text data type. So, I go and change the type to short date. The error still occurred. I have tried changing the format to number, to long date, back to general, and so forth. For some reason the format is not changing. I also tried copying the data and pasting only the values then started working with the pasted data. That didn't work either. I discovered that If I put my cursor in the cell then click out of it, the format is applied.

This is before. Cell B1 shows the date. The format for the entire column was changed to short Date but as shown the format did not change.

Click in the field next to the formula button that shows the content of the cell. The cursor shows up at the end of the value.

Get out of that field by clicking on a different cell.

Then as shown above, cell B1 will automatically change to the designated number format, which was Short Date.

I have never encountered this before. Maybe this question is to wordy, but I thought it would be noteworthy to show my observation.

How do I apply the number format to the entire column for this case, where even using Paste Special - Value didn't work?

Thank you.

Answer 1

Based on the information you provided, it seems that the issue might be related to the formatting of the data in the cells. When you change the format of a cell, it only changes the way the data is displayed, not the actual data itself.

To change the data type of the cells, you can try using the Text to Columns feature. Here are the steps:

1. Select the column of dates that you want to convert.
2. Go to the Data tab and click on Text to Columns.
3. In the Convert Text to Columns Wizard, select Delimited and click Next.
4. In the next screen, uncheck all the delimiter options and click Next.
5. In the next screen, select Date and choose the appropriate date format from the dropdown menu.

6. Click Finish to convert the text to dates.

This should convert the text data to date format, and you should be able to use the pivot table function without any issues.

Answer 2

Thank you! That worked. Now I can continue with my task, which was to figure out if a pivot table can give me the average days between dates without the use of formulas and helper cells.

Answer 3

You are welcome:)