Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

DateValue function

Copper Contributor

Years ago, I set up a spreadsheet to monitor and graph my blood pressure, using the DATEVALUE function to convert text dates to numerical dates. When trying to update the table today, I discovered that =DATEVALUE(B3) where B3 is "DD/MM/YYYY" produces a "VALUE" error.  My file hasn't changed, but the DATEVALUE formulas no longer work. Does anyone know what has happened here?

6 Replies

@ChetInSanJose 

 

The quickest and best way to get a constructive answer is to attach an example Excel file.  Click "browse" at the bottom of the reply window, or upload the file to a file-sharing website and post the download URL.

 

At a minimum, you should show us what is in B3 and what does ISTEXT(B3) return.  Typically, a #VALUE error means one of two things:  B3 contains a number, not text; or Excel cannot recognize the form of the text as a date, based on the current system configuration.

 

Note the latter:  "current system confirguration".  That might have changed, unbeknownst to you, especially if you use Office 365 (based on other users' experience).

@Joe User The problem is that these formulas worked for years, but when I opened the file again after a year, the formulae now don't work.  I didn't change anything at all in the workbook. It seems to me, therefore, that something has changed in Excel.  When I get to my computer I'll send you the info you asked for.  Thanks much for trying to help.

Kindest regards,

C. Aeschliman 

Adding to Joe's comment regarding "system configuration," you might see if the systems short date format setting has changed (in windows control panel). If the system's short date format is mm/dd/yyyy and you have a date like 24/09/2022, then you would get a value error because what it thinks is the month is >12.

@Joe User Here are the cells' contents.  The formulae worked for years, but today they no longer do.

 

B3 'Jul. 16
C3 '18H14 (I use a 24 Hr. clock)
E3 =DATEVALUE(CONCATENATE(RIGHT(B3,2),"/","1","/",ThisYear))+TIMEVALUE(CONCATENATE(LEFT(C3,2),":",RIGHT(C3,2),":","00"))

 

The objective is to get an Excel date function for the exact time of day on a particular day.

 

Joe User suggests there may be a change in the date function causing this.  I'll be looking at that.

 

Thanks again.

I just opened the file again, and now the formulae magically work again. I seem to have wasted everybody's time. Problem solved, but I don't know how. Incidentally, "ThisYear" is a range name currently having the value 2022. Thanks, everyone for trying to help me thru this mystery!

@ChetInSanJose  wrote:  `` I seem to have wasted everybody's time. Problem solved, but I don't know how.``

 

 

Maybe not.  As I wrote:  ``That [date configuration] might have changed, unbeknownst to you, especially if you use Office 365 (based on other users' experience)``.

 

I do not use Office 365 myself, for the very reason that MSFT seems to "update" it frequently with apparently poorly tested changes.

 

Again, I base that conclusion on other users' questions/complaints that are posted here and in other Excel forums.  The conclusion might be wrong, based on biased and unknowledgeable comments.

 

(I also object to paying a periodic "subscription" fee for something that I used to pay a one-time purchase price.)

 

PS....  I confirm that when the system date configuration is d/M/yyyy, the DATEVALUE formula in E3 works with the text data in C3 and D3 and with 2022 in a cell named ThisYear.

 

OTOH, I also confirm that the formula returns #VALUE if the system date configuration is M/d/yyyy, which is the default for San Jose, CA, USA.