DATEVALUE function not working

[davidam]

Hello Everyone,
I just acquired a new computer and a newly purchased copy of Excel 2021. A spreadsheet that I have been working with for years has a new problem. The DATEVALUE function returns #VALUE for everything.
I downloaded and repaired my copy of office and the problem was not fixed. I imagine this is a mission critical feature for many users; it certainly is for me. Has anyone else noticed this? Maybe my new computer is glitchy, although I doubt it; when I put =Today() into a cell if returns the proper date. When I reference that cell with DATEVALUE, I get #VALUE.
Thanks,
David

 

[Fluff]

when I put =Today() into a cell if returns the proper date. When I reference that cell with DATEVALUE, I get #VALUE.

That is exactly what I would expect to happen. Datevalue converts a text representation of a date into a proper date.

 

[davidam]

That is exactly what I would expect to happen. Datevalue converts a text representation of a date into a proper date.

It also converts a proper date to a date when it is working properly. I did that as a test of the function. There are 3 other text versions of a date that are not working properly on the same sheet.

 

[Fluff]

It will return #VALUE! if you give it a proper date (ie a number) & as far as I know, has always done that.

 

[davidam]

When I think back to why I was using this like this. I think it was because Excel would not compare dates that had different formats. Using DATEVALUE always allowed me to level the playing field so to speak.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[davidam]

Glad to help & thanks for the feedback.

Actually, I think there is an issue with the program.
These are the formulas that always worked before:
BE34=Vlookup of below returning 9 AA34 AD34

=DATEVALUE(B34)

=BE34&"/"&AA34&"/"&AD34

September

1

2018

And this is the result:

#VALUE!

9/1/2018

September

1

2018

 

[Fluff]

If BE34 has a value of 9 then I would expect the function to return an error. It should be looking at the cell that contains

=BE34&"/"&AA34&"/"&AD34

 

[davidam]

One more strange thing: I closed and opened the file a few times and after opening it the last time, the function is suddenly working. I wonder if that says something about the hardware?

 

[davidam]

B34 has a value of

=BE34&"/"&AA34&"/"&AD34

and then DATEVALUE points to B34
Sorry for the messy replies