Forum Discussion

  • mlpilling's avatar
    mlpilling
    Copper Contributor

    Jonathan_2112  - this is utterly stupid but the only way to fix this (for me, in canada) is to change regional language and date settings (on the PC) to English (USA). 

    #microsoftbugs

     

     

    • HansVogelaar's avatar
      HansVogelaar
      MVP

      Jonathan_2112 

      If the cell used as argument already contains a "real" date, there is no point in using DATEVALUE.

      DATEVALUE expects a text string that looks like a date as argument.

      If A1 contains the date 10/09/22, =DATEVALUE(A1) will return #VALUE!

      If A1 contains a text value that looks like 10/09/22, =DATEVALUE(A1) will return the date 10/09/22.

      • Jonathan_2112's avatar
        Jonathan_2112
        Copper Contributor
        OK, yes, but I wish to create the serial number form of the date for sorting.
  • Jonathan_2112 

    What are you using ar argument for the DATEVALUE function?

    And what is your system date format? M/D/Y as in the USA, D/M/Y as in many European countries, or Y/M/D as in the ISO date format?

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    Jonathan_2112 

    From the help article.

     

    Date_text    Required. Text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates.

    Using the default date system in Microsoft Excel for Windows, the date_text argument must represent a date between January 1, 1900 and December 31, 9999. The DATEVALUE function returns the #VALUE! error value if the value of the date_text argument falls outside of this range.


     


     

Resources