DATEVALUE works for colleague but not me

oladunken

New Member
Joined
Sep 7, 2017
Messages
8
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
What are your Windows date settings?
 
Upvote 0
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.
 
Upvote 0
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,".","/")))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top