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)

Improve this question
3
  • 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
    Commented Jul 28, 2022 at 6:49
  • @JosWoolley That's correct. Excel online uses US date format.
    – Reddy Lutonadio
    Commented Jul 28, 2022 at 6:51
  • Refer to this support article about change region for Excel online: support.microsoft.com/en-us/office/….
    – Lee
    Commented Jul 28, 2022 at 9:15

1 Answer 1

Reset to default
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.

Improve this answer
1
  • Thank you, i used =MID(C4, 4, 2)&"/"&LEFT(C4, 2)&"/"&RIGHT(C4, 2)) +0 to get the date into the right format
    – Servus
    Commented Aug 2, 2022 at 6:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .