DATEVALUE function not working

davidam

Active Member
Joined
May 28, 2010
Messages
497
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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.
 
Upvote 0
It will return #VALUE! if you give it a proper date (ie a number) & as far as I know, has always done that.
 
Upvote 0
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.
 
Last edited:
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
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&"/"&AD34September12018

And this is the result:

#VALUE!9/1/2018September12018
 
Upvote 0
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
Excel Formula:
=BE34&"/"&AA34&"/"&AD34
 
Upvote 0
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?
 
Upvote 0
B34 has a value of
Excel Formula:
=BE34&"/"&AA34&"/"&AD34
and then DATEVALUE points to B34
Sorry for the messy replies
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

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