1

I noticed that =DATEVALUE() behaves different in the online version of Excel 365 (i.e. it does not work) than in the local version of Excel 365 on my PC. Below are two screenshots of the two Excel Versions.

=DATEVALUE() in Excel 365 online

=DATEVALUE() in Excel 365 on my local PC

Any idea on how I can get the formula to work in both Excel versions? I cannot use VBA, unfortunately.

(I used "TT.MM.JJ" as the text_format parameter, as my local Excel is set to German)

Servus
  • 97
  • 7
  • 1
    I imagine your locale settings are different in Excel Online. Recognised date formats are dependent on the system locale. In my English-language version of Excel, 28.07.22 is not a valid date type. I presume it is for German-language versions. Perhaps your Excel Online language settings are set to US, for example? – Jos Woolley Jul 28 '22 at 06:49
  • @JosWoolley That's correct. Excel online uses US date format. – Reddy Lutonadio Jul 28 '22 at 06:51
  • Refer to this support article about change region for Excel online: https://support.microsoft.com/en-us/office/change-regional-format-settings-in-excel-for-the-web-c206344b-0d45-4a82-8eee-34ff6b008e9c#:~:text=Set%20the%20preferred%20regional%20format%20in%20Excel%20for,Your%20browser%20session%20refreshes%20using%20the%20new%20setting. – Lee Jul 28 '22 at 09:15

1 Answers1

2

You are getting the error due to the regional settings being different. As your pictures show values on B3, online uses "MM/DD/YYYY" settings while your local Excel uses "DD.MM.YYYY" ("TT.MM.JJJJ").

When you set on B4 the text format to "DD.MM.YY" in Excel online, that text is not recognize as date when you use DATEVALUE. That's why you are getting #VALUE.

Reddy Lutonadio
  • 17,120
  • 4
  • 14
  • 35
  • Thank you, i used =MID(C4, 4, 2)&"/"&LEFT(C4, 2)&"/"&RIGHT(C4, 2)) +0 to get the date into the right format – Servus Aug 02 '22 at 06:15