#VALUE! error when using TIMEVALUE or DATEVALUE function

Question

Can anyone tell me why the formula is B6 is giving the #VALUE! error.

The formula in B8 is =TIMEVALUE(A8). The cell format of A8 is TEXT

FYI this happens when I use DATEVALUE and VALUE function as well. I'm using O365 v1908.

Answer 1

The error is because that short date format is set to ddd d/M/yy.

This doesn't work when you have ddd in front of the date format. You need to change this format to a regular date format say dd/mm/yy and the error will disappear.

Answer 2

I note #VALUE error for

TIMEVALUE("27/05/2020 12:18 p.m.")

This is because you can't use p.m. but pm

Then I notice #VALUE for =VALUE(TEXT(F3,"ttt"))+(12/24) and =VALUE(TEXT(F4,"ttt"))+(12/24)

The reason here is TEXT(F3,"ttt") will evaluate to ttt. Hence operating VALUE on this will give #VALUE.

Anywhere else do you see #VALUE.

This is weird. All the values in column were showing as #VALUE!. Apparently when you opened the same sheet in your Excel, they corrected themselves. 

This is how it looks for me:

![](https://learn-attachment.microsoft.com/api/attachments/4bffa744-18c5-4623-aa49-124f6c0357d5?platform=QnA

Answer 3

Windows key, hold it and press r

Type intl.cpl and enter

Post the screenshot of this.

Answer 4

Windows key, hold it and press r

Type intl.cpl and enter

Post the screenshot of this.

Answer 5

The error is because that short date format is set to ddd d/M/yy.

This doesn't work when you have ddd in front of the date format. You need to change this format to a regular date format say dd/mm/yy and the error will disappear.

Good work, Arya. That seems to have resolved the issue. However, it is ridiculous that Excel is not region agnostic. In this globalised age, we share documents all over the world. Can't expect everyone to make regional settings adjustments to see data in their Excel sheet! Wasted hours of my life as I had two colleagues in other parts of the world who had to make similar adjustments. Thanks again for your time.