Changing cell number format not working as expected

Question

Changing cell number format not working as expected

HuntM 20

I have copied a set of dates from word into excel.

When I try to change the format of the column (i.e. "short date") the data does not change.

When I try to "sort" by these dates, it is not reading them as dates, and is sorting them numerically rather than by date.

I tried follow the advice in this ask (https://learn.microsoft.com/en-us/answers/questions/5268610/changing-cell-number-format-not-working-as-expected) but it appears to be for a different version of microsoft as I did not get the final menu option to "choose the column data format".

What do I need to do?

Thanks!

0 comments

Answer accepted by question author

Jayden-P 2,845 Independent Advisor

Hi, @HuntM

It sounds like Excel may be treating the values as text rather than true date values, which would explain why changing the cell format has no effect and why the sort order is not chronological.

As a quick check, click one of the affected cells and look for any signs that Excel is storing it as text.

For example, is the value left-aligned rather than right-aligned, or does the formula =ISNUMBER(A1) (using one of the affected cells) return FALSE? You can also click on the cell and check the format of that cell. If any of these apply, Excel is likely treating the values as text.

User's image

You mentioned you tried Text to column from the thread and did not see final menu option, could you please provide your Excel version as well as screenshots? Before sharing the screenshot, please ensure that any personal, confidential, or sensitive information is removed.

0 comments

1 additional answer

Your answer

Answer 1

HuntM 20

Hi,

I was using Excel online. I tried in Excel desktop and I was able to use the "text-to-column" method.

It was treating the data as text (left-aligned, =ISNUMBER(A1) returned FALSE).

Thanks for your advice! It was asking about the version of microsoft which prompted me to open in desktop. I have attached a picture of the Excel Online "text-to-column" menu below for completeness- as you see there is no option to change the format. User's image

Jayden-P 2,845 Reputation points Independent Advisor

2026-08-11T09:03:12.9+00:00

Hi @HuntM

Thanks for the update, and I'm glad you were able to get it working.

That makes sense. Since the values were being stored as text (as confirmed by the left alignment and =ISNUMBER() returning FALSE), using Text to Columns in the desktop app allowed Excel to convert the text strings into true date values.

I tested this in Excel for the Web and observed the same behavior. The Split Text to Columns experience in Excel Online does not provide the Column data format option that is available in the desktop application.

I'm pleased to hear that opening the file in the desktop application resolved the issue. If you found my advice helpful, please kindly mark my answer as accepted answer.

Warm thanks.