DATEVALUE works for colleague but not me

[oladunken]

Seems there's always trouble with date values. Maybe it's more frequent in non-english countries due to locale settings? I don't know, but either way date conversions is a real pain.

I was sent an excel sheet from a colleague at work today.
He has date as text a cell ...let's call it cell1. The text reads 02.01.2019 . So this means January 2nd 2019 and is a quite common way of daate notation in Norway. He converts this textdate to a real but similar date by using =DATEVALUE(cell1). This works fine is his spreadsheet and the new cell has a real date looking exactly like the textdate from cell1.
However, when I tried to use his excelllllsheet i gggget #VALUE ! in this cell.

Can anyone explain what I need to do and why is there a difference in behaviour? I assume he's oooooooon the same excel version. Could be differnces in keyboard settings/language though.
Is there something about locale/language/versions etc?
I'm on windows10 Excel office 365 btw.

 

[MARK858]

What are your Windows date settings?

 

[oladunken]

What are your Windows date settings?

Do you know where I can find these settings?
I tried to find this on my PC. I found something I think was right from menus connected to the showing of time and date in one ofthe menubars. However, after making changes so that date was shown in dd.mm.yyyy format instead of mm/dd/yyyy it didn’t seem to affect excel sheet #VALUE! problem after reopening sheet.

Ps: sorry for typos in first posting. Had keyboard issues.

 

[FormR]

Hi, it's in "region and langauge" in the "control panel" but you probably don't want to mess with that, it will affect more than just Excel.

As an alternative you could try:

=DATEVALUE(SUBSTITUTE(cell1,".","/"))

Or if you need it to work for both of you.

=IFERROR(DATEVALUE(cell1),DATEVALUE(SUBSTITUTE(cell1,".","/")))

 

[oladunken]

I tried to find this on my PC. I found something I think was right from menus connected to the showing of time and date in one ofthe menubars. However, after making changes so that date was shown in dd.mm.yyyy format instead of mm/dd/yyyy it didn’t seem to affect excel sheet #VALUE! problem after reopening sheet.

It seems a restart of the PC was needed for the new time settings to take effect. It works now.

 

[oladunken]

Hi, it's in "region and langauge" in the "control panel" but you probably don't want to mess with that, it will affect more than just Excel.

As an alternative you could try:

=DATEVALUE(SUBSTITUTE(cell1,".","/"))

Or if you need it to work for both of you.

=IFERROR(DATEVALUE(cell1),DATEVALUE(SUBSTITUTE(cell1,".","/")))

Thanks!
Will see if we can use the IFERROR option in the original file even if it works for me now after the time setting change (and restart) of my PC.

 

[MARK858]

Hi, it's in "region and langauge" in the "control panel" but you probably don't want to mess with that, it will affect more than just Excel.

Just for info I didn't say the OP would want to mess with them. I wanted to know what they were to see if we could get away with a simple Find/Replace in the sheet.

 

[FormR]

Just for info I didn't say the OP would want to mess with them.

Hi Mark - I didn't think for a minute that you were suggesting they should.