How to use a variable instead of a literal in EXCEL DATEVALUE()

Question

In the specifications for EXCEL's DATEVALUE function(see below***), the DATE parameter is defined as a literal in quotes. But in the spreadsheet in question, I enter a date value each time a medication is refilled. Is there another EXCEL function that accepts the date as a cell reference rather than a value in quotes? If so, it would avoid having to key the date twice.

Thanks for your help.
SweetTasha

***SPECIFICATIONS FOR EXCEL's DATEVALUE function

https://support.microsoft.com/en-us/office/date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81

Answer 1

Hello SweetTasha,

I am Ibhadighi and I would happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

In Microsoft Excel, you can indeed use a cell reference instead of a literal date value in the DATEVALUE function. Here's how you can do it:

Suppose you have a date in cell A1, and you want to convert it using DATEVALUE. Instead of using a literal date like this: =DATEVALUE("2023-08-29")

You can reference the cell containing the date, like this: =DATEVALUE(A1)

This formula will convert the date in cell A1 into a serial number that Excel can work with. Just make sure that the cell referenced (in this case, A1) contains a valid date in a format that Excel recognizes. Excel is usually good at recognizing various date formats, but it's a good practice to format the cell as "Date" to ensure proper recognition.

Here are the steps to format a cell as a date:

1. Select the cell containing the date (A1 in this example).
2. Right-click on the selected cell.
3. Choose "Format Cells" from the context menu.
4. In the "Number" tab, select "Date" from the category list.
5. Choose the desired date format from the list.
6. Click "OK."

Now, the cell will display the date in your chosen format, and you can use it in the DATEVALUE function without the need for quotes.

Best regards, Ibhadighi.

Answer 2

Dighi,

Thanks for your reply. That resolves my issue.

SweetTasha.