Excel is a powerful tool for analyzing and managing huge datasets in tabular form. One of its key strengths is the ability to handle dates effectively.
The DATEVALUE is one of the most used functions when it comes to dates in Excel.
It helps you convert the date strings into a serial number that normal users can understand.
In this article, we will explore how to use the DATEVALUE function in Excel, from formulas to use cases. Make sure to read this article until the end because it contains all you need about the DATEVALUE function.
Why convert dates to serial numbers?
Converting dates to serial numbers in Excel is important because it allows users to perform complex calculations quickly.
Dates in Excel may represent anything from task deadlines, hire or exit dates, birthdays and event schedules. Handling dates in Excel can be a challenging task as they can be written in various formats. Refer to the following image,
The above dates humans can easily understand. However, they may confuse spreadsheet programs like Excel, leading to errors while calculating, filtering, and sorting datasets by dates.
Excel uses serial numbers to perform logical and mathematical operations to avoid errors that might arise from different date formats.
Such conversion enhances the functionality and versatility of date-related data in Excel.
Also, the DATEVALUE formula is a popular way to check if the dates entered are acceptable in Excel. If you wish to validate a certain date format, use the DATEVALUE function and see the results.
Explained: The DATEVALUE Function
The DATEVALUE is one of the simple and useful functions in Excel. The general syntax of this formula is as follows,
=DATEVALUE(date_string)Here, the “date_string” is the date in text format or the cell reference containing the date string that you want to convert.
If you choose to provide the cell reference in the place of the date_string argument, make sure the respective cell contains a text string. It shouldn’t be a number or date; otherwise, the function will return “#VALUE!” error.
In other words, in case of cell references, the DATEVALUE function will only work if the cell contains text value and not a date or number.
Download the Example Excel
Before we jump into the steps to use the DATEVALUE function in Excel, make sure you download the following Excel containing the spreadsheets and the data used to demonstrate various use cases.
Also, follow me along as we move through this article for a better understanding.
If you have your spreadsheet ready with the required date data to practice, you can skip downloading the above spreadsheet.
How to use the DATEVALUE function in Excel
You don’t require any experience to use the DATEVALUE function in Excel.
Here is an example dataset that includes employee names and hire dates. Our task is to convert hiring dates into serial numbers.
Let’s begin,
- Click on the desired cell
- Type “=DATEVALUE”
- Select the first option from the popup or press “Tab” on your keyboard
- Replace the date_string argument with the cell reference “B2”
- Close the brackets using “)”
- Press “Enter” on your keyboard
Here’s how the final formula should look,
=DATEVALUE(“B2”)To apply this formula to the rest of the cells, you can use the Auto Fill option. You need to hover to the lower-rightmost corner of the cell until you see the “+” icon. Then, click on that “+” icon and drag the formula to the end of the table.
Excel will instantly convert all of the valid dates to serial numbers.
If any of the hiring dates could be in incorrect format, Excel would have returned an error as the “DATEVALUE parameter cannot be parsed to date/time.”
What is another way to access the DATEVALUE function in Excel?
There are two methods to access the DATEVALUE formula in Excel: manually type the formula followed by the “=” sign, or select it from the main menu.
The first option is pretty straightforward.
Let’s see how to insert the function from the main menu.
- Open the Excel Sheet
- Click on the desired cell
- Hover to the main menu and click on the “Formulas” tab
- Choose the “Insert function” option from the popup
- An “Insert function” window will be opened
- Select the formula that you want to insert from the available options or search for it in the Search bar at the top
- Once you select the formula, you will be prompted to enter the argument values also, as shown below
What are Excel-compatible Date Formats?
Dates can be written in different formats. However, not all date formats can be interpreted as valid dates in Excel.
Here is a list of acceptable date formats in Excel.
| Date Format | Date Format in Excel | Example |
|---|---|---|
| Month – Date – Year | mm-dd-yyyy | 08-20-2023 |
| Year – Month – Date | yyyyy-mm-dd | 2023-08-20 |
| Date – Month- Year | dd-MM-yyyy | 20-Aug-2023 |
| Month – Date – Year | MM-dd-yyyy | Aug-20-2023 |
| Month / Date / Year | mm/dd/yyyy | 08/20/2023 |
| Year / Month / Date | yyyyy/mm/dd | 2023/08/20 |
| Date / Month / Year | dd/MM/yyyy | 20/Aug/2023 |
| Month / Date / Year | MM/dd/year | Aug/20/2023 |
| Month, Year | MM, yyyy | August, 2023 |
| Month Date, Year | MM dd, yyyy | August 20, 2023 |
Now, let us use the DATEVALUE function for the above dates and check if they are valid and converted into serial numbers.
Note that you can use the separators like dash (-), slash (/), and space ( ).
These are the most common date formats you may wish to use most of the time. To explore more date formats, you can follow the steps below:
- Highlight the cells you want to format
- Right-click on the selected cells
- Click on “Format Cells”
- A new dialogue box called “Format Cells” will be opened
- In the “Category” list, select “Custom”
- Scroll down to see the predefined date formats
To be compatible with the DATEVALUE function, make sure that your provided date format follows one of these predefined formats.
I am facing a #VALUE! Error while using the DATEVALUE function
You may come across a #VALUE! Error sometimes when using the DATEVALUE function. Let’s discuss the most common scenarios where this error occurs.
Scenario #1
The date_text argument should be a valid text value and not a number or a date. For example, 25 May 2020 is a valid date, but the following are not,
Solution: You need to right-click on the desired cell, choose the “Format Cells” option from the popup and make sure the cell follows the “Text” format. If not, then change the formatting to text.
Scenario #2
The value in the date_text argument is not in sync with the system’s date and time settings.
If your system’s date and time settings follow the mm/dd/yyyy format, then the formula =DATEVALUE(“29/02/2002”) will return a #VALUE! Error. But it will display the correct value when the system’s date and time are set to dd/mm/yyyy.
Solution: Make sure your system’s time and date settings (both Short and Long time) match the date format in the date_text argument.
Scenario #3
The date mentioned is not between January 1, 1990 and December 31, 9999.
Solution: Make sure that the date_text argument represents a date between January 1, 1990 and December 31, 9999.
What is the quickest way to check if the Date is formatted properly?
All dates that are formatted correctly are aligned to the right of the cell. Refer to the following image, where the first three cells contain valid dates, and the remaining two cells contain invalid dates.
Note that while using this method to check the date formatting, you must ensure that the cell’s width is more than the width of the content (date entered) in the cell.
To Summarize: Excel DATEVALUE Function
The DATEVALUE function in Excel is a powerful tool for converting date strings into serial numbers, making date calculations and comparisons more efficient.
By converting dates into serial numbers, you avoid errors that arise from different date formats, allowing for more accurate calculations, filtering, and sorting.
Make sure you properly understand common errors and their solutions, ensuring that you can troubleshoot and correct issues as they arise.
If you are stuck somewhere or still have any doubts, feel free to comment below.
Make sure to explore our blog section for more tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.