Share via

Trying to change date format in Excel using "Format" option but it has no effect

Anonymous
2021-03-31T22:45:27+00:00

Am trying to change date format in Excel using "Format" option but it has no effect

* Moved from Internet Explorer(other)/Windows(other)

* Original title: Date change

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. HansV 462.4K Reputation points MVP Volunteer Moderator
    2021-04-01T18:22:03+00:00

    If you see a date in a cell but changing the number format has no effect, the cell probably contains a text value that looks like a date. Try the following:

    • Apply a date format.
    • Press F2, then press Enter.

    Does that help?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2021-04-01T18:43:24+00:00

    You cells probably contain date that is type text.  Use formulas of the form =ISTEXT(A1) to confirm.  Looks are deceiving; and the cell format does not matter.

    If you are having trouble determining why Excel treats the values as text, it would be best if you upoad an example Excel file (redacted) that demonstrates the problems to a file-sharing website,

    and post the download URL in a response here.  I like box.net/files; others like dropbox.com.

    Do not use onedrive.live.com; sometimes, it changes the structure of the workbook.

    In any case, test the download URL first, being careful to log out of all websites that share the same login,

    in order to ensure that anyone else can access the file anonymously.

    Common causes are:

    1. The form of the "dates" does not match what the system expects, usually specified in the Region and Language control panel.
    2. The "dates" have errant spaces.
    3. The "dates" contain acceptable spaces, but they are "non-breaking" spaces (HTML nbsp; ASCII 160).
    4. The data contains Unicode characters.  To see their code, use formulas of the form =UNICODE(MID(A1,COLUMNS($A$1:A1),1)), which you copy across the row for LEN(A1) cells.

    And that's just the tip of the iceberg.

    0 comments