DATEVALUE() Different Behavior in Excel 365 Online and Local

microsoft excelmicrosoft-excel-365sharepoint-onlineworksheet-function

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.

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)

Best Answer

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.

Related Solutions

Excel – Rewriting a formula of multiple nested IFs that is too deep

I think the formula is equivalent to:

=IF(OR(N7>=100000000,N7<500000),0,100-INT(N7/1000000))

Now that Kevin has reformatted the formula, I can see quite clearly that the formula is correct in all cases.

Thanks to the questioner's comment, I can state that the equivalent German formula is:

=WENN(ODER(N7>=100000000;N7<500000);0;100-GANZZAHL(N7/1000000))

Excel 365 – Locale Independent Text Formatting of Numbers

As local-independent formatting may be impossible without VBA, you could perhaps adapt your Excel code to become multi-locale.

The idea is to use VLOOKUP on a value that will change based on the system language.

The example below will use the local name of the month of January to identify the locale as English or German for the formatting of a date field:

=VLOOKUP(TEXT(1,"MMMM"),{"January","yyyy-MM-dd";"Januar","jjjj-MM-tt"},2,FALSE)

In English the month is called "January", but in German it's "Januar".

We do the VLOOKUP on the parameter array below to get the right format string:

"January" , "yyyy-MM-dd" ;
"Januar"  , "jjjj-MM-tt"

You could also store the right format string for the locale in a cell and use this value in all the TEXT functions of this data-type, instead of repeating the VLOOKUP call on each.

(This answer is based on the StackOverflow post Locale-independent Text function in Excel.)

Best Answer

Related Solutions

Excel – Rewriting a formula of multiple nested IFs that is too deep

Excel 365 – Locale Independent Text Formatting of Numbers

Related Question