Forum Discussion

Binghan's avatar
Binghan
Copper Contributor
Sep 05, 2023

Why Excel read "31/12/2022" as text instead of date?

Hi there,    My Excel file reads one cell "31/12/2022" as text rather than date.  This caused error in other cells with formula referencing to this file (should be read as date but it is text so re...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Sep 05, 2023

Binghan 

Did you use a different date format in Windows (or Mac) when you originally entered 31/12/2022?

If your system date format at the time was m/d/yyyy, as is usual in the USA, 31/12/2022 would not be recognized as a date.

  • Binghan's avatar
    Binghan
    Copper Contributor
    Sep 05, 2023

    HansVogelaar 

     

    Hi Hans, 

    Thanks for your reply.

    The dates in other cells are working well... that's the part puzzled me. Please find the screen shot below (the left column is the original dates I input and the right column is using formula "=xxx" so only that cell returns text). 

    Cheers, 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Sep 05, 2023

      Binghan 

      Apparently, Excel interpreted the value as text when you entered it. There are several possible causes, but it's impossible for us to determine which one it actually was.

      • Binghan's avatar
        Binghan
        Copper Contributor
        Sep 05, 2023

        HansVogelaar 

        Thanks Hans.  Is it possible for you to give me some examples of possible causes? so that I can trace down what I did and locate the most likely cause. 

        Cheers, 

Resources