# DATEVALUE Function in Microsoft Excel

## Part 1: Introduction

### Definition

The DATEVALUE function in Microsoft Excel is a date and time function that converts a date stored as text into a serial number that Excel recognizes as a date.

### Purpose

The DATEVALUE function is handy when a worksheet contains dates in a text format you want to filter, sort, or format as dates or use in date calculations.

### Syntax & Arguments

The syntax for the DATEVALUE function is as follows:

syntax
```DATEVALUE(date_text) ```

Here, the argument is:

• date_text: This is a required argument. It represents a date in an Excel date format or a reference to a cell that contains text that describes a date in an Excel date format. For example, “1/30/2008” or “30-Jan-2008” are text strings within quotation marks representing dates.

### Return Value

The DATEVALUE function returns a serial number that Excel recognizes as a date. For instance, the formula `=DATEVALUE("1/1/2008")` returns 39448, the serial number of the date 1/1/2008.

### Remarks

Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,447 days after January 1, 1900. Most functions automatically convert date values to serial numbers.

## Part 2: Examples

### Example 1

Purpose of Example: To convert a date in text format to a date serial number for further calculations.

Data Tables and Formulas

ABC
1Date (Text)Date Serial NumberResult
21/1/2023`=DATEVALUE(A2)`45197
32/1/2023`=DATEVALUE(A3)`45228
43/1/2023`=DATEVALUE(A4)`45256

Explanation: In this example, we have dates in text format in column A. In column B, we use the DATEVALUE function to convert these text dates into date serial numbers that Excel can use for calculations.

### Example 2

Purpose of Example: To sort dates that are in text format.

Data Tables and Formulas

ABC
1Date (Text)Date Serial NumberResult
23/1/2023`=DATEVALUE(A2)`45256
31/1/2023`=DATEVALUE(A3)`45197
42/1/2023`=DATEVALUE(A4)`45228

Explanation: Here, we have dates in text format that are not chronological. Using the DATEVALUE function to convert these to date serial numbers, we can sort these dates in ascending or descending order.

### Example 3

Purpose of Example: To filter dates that are in text format.

Data Tables and Formulas

ABC
1Date (Text)Date Serial NumberResult
21/1/2023`=DATEVALUE(A2)`45197
32/1/2023`=DATEVALUE(A3)`45228
41/1/2024`=DATEVALUE(A4)`45562

Explanation: In this example, we have dates in text format that span multiple years. Using the DATEVALUE function to convert these to date serial numbers, we can filter these dates to only show dates from a specific year.

### Example 4

Purpose of Example: To calculate the difference between two dates that are in text format.

Data Tables and Formulas

ABCD
1Start DateEnd DateDifference in DaysResult
21/1/20231/1/2024`=DATEVALUE(B2)-DATEVALUE(A2)`365
32/1/20232/1/2024`=DATEVALUE(B3)-DATEVALUE(A3)`365
43/1/20233/1/2024`=DATEVALUE(B4)-DATEVALUE(A4)`365

Explanation: In this example, we have start and end dates in text format. We use the DATEVALUE function to convert these to date serial numbers and then subtract the start date from the end date to calculate the difference in days.

### Example 5

Purpose of Example: To calculate the number of days until a future date from today.

Data Tables and Formulas

ABCD
1TodayFuture DateDays Until Future DateResult
28/2/20231/1/2024`=DATEVALUE(B2)-DATEVALUE(A2)`152
38/2/20232/1/2024`=DATEVALUE(B3)-DATEVALUE(A3)`183
48/2/20233/1/2024`=DATEVALUE(B4)-DATEVALUE(A4)`211

Explanation: In this example, we have today’s date and a future date in text format. We use the DATEVALUE function to convert these to date serial numbers and then subtract today’s date from the future date to calculate the number of days until the future date.

### Example 6: Using DATEVALUE with IF

Purpose of Example: To determine if a given date in text format falls on a weekend.

Data Tables and Formulas

AB
1Date (Text)Result
21/1/2023`=IF(WEEKDAY(DATEVALUE(A2),2)>5, "Weekend", "Weekday")`

Explanation: In this example, we use the DATEVALUE function to convert the date in text format into a serial number. Then, we use the WEEKDAY function to determine the day of the week (with Monday as 1 and Sunday as 7). Finally, the IF function checks if the day is greater than 5 (Saturday or Sunday), returning “Weekend” if true and “Weekday” if false.

### Example 7: Using DATEVALUE with SUM

Purpose of Example: To calculate the total number of days between pairs of start and end dates.

Data Tables and Formulas

ABC
1Start Date (Text)End Date (Text)Total Days
21/1/20232/1/2023`=SUM(DATEVALUE(B2)-DATEVALUE(A2), DATEVALUE(B3)-DATEVALUE(A3))`
33/1/20234/1/2023

Explanation: We have pairs of start and end dates in text format. We use the DATEVALUE function to convert these text dates into date serial numbers and subtract the start dates from the end dates. The SUM function adds up these differences, giving us the total number of days between the pairs of dates.

### Example 8: Using DATEVALUE with VLOOKUP

Purpose of Example: To find the corresponding value for a given date using VLOOKUP.

Data Tables and Formulas

ABC
1Date (Serial)ValueLookup Date (Text)
2451971001/1/2023
345228200
445256300
5`=VLOOKUP(DATEVALUE(C2), A2:B4, 2, FALSE)`

Explanation: In this example, we have a table of date serial numbers and corresponding values in columns A and B. In column C, we use the DATEVALUE function to convert a lookup date in text format into a date serial number. Then, we use the VLOOKUP function to find the corresponding value for that date. The result is the value that corresponds to the lookup date.

### Example 9: Using DATEVALUE with AVERAGE

Purpose of Example: To calculate the average number of days between pairs of start and end dates.

Data Tables and Formulas

ABC
1Start Date (Text)End Date (Text)Average Days
21/1/20232/1/2023`=AVERAGE(DATEVALUE(B2)-DATEVALUE(A2), DATEVALUE(B3)-DATEVALUE(A3))`
33/1/20234/1/2023

Explanation: Similar to Example 7, we have pairs of start and end dates in text format. We use the DATEVALUE function to convert these text dates into date serial numbers and subtract the start dates from the end dates. The AVERAGE function calculates the average number of days between the pairs of dates.

### Example 10: Using DATEVALUE with CONCATENATE

Purpose of Example: To create an entire date string by concatenating a date in text format with a specific message.

Data Tables and Formulas

AB
1Date (Text)Result
21/1/2023`=CONCATENATE("The meeting is on ", TEXT(DATEVALUE(A2), "dddd, mmmm d, yyyy"))`

Explanation: In this example, we use the DATEVALUE function to convert the date in text format into a serial number. Then, we use the TEXT function to format the date in a specific way (e.g., “Sunday, January 1, 2023”). Finally, the CONCATENATE function combines this formatted date with a particular message.

### Example 11: Using DATEVALUE with COUNTIF

Purpose of Example: To count how many dates in a list are before a specific date.

Data Tables and Formulas

ABC
1Date (Text)Specific Date (Text)Count
21/1/20232/1/2023`=COUNTIF(A2:A4, "<"&DATEVALUE(B2))`
31/15/2023
41/30/2023

Explanation: In this example, we have a list of dates in text format in column A and a specific date in text format in column B. We use the DATEVALUE function to convert the date into a date serial number and then the COUNTIF function counts how many dates in the list are before the specific date.

### Example 12: Using DATEVALUE with MAX and MIN

Purpose of Example: To find the latest and earliest dates in text format from a list of dates.

Data Tables and Formulas

ABC
1Date (Text)Latest DateEarliest Date
21/1/2023`=TEXT(MAX(DATEVALUE(A2:A4)), "mm/dd/yyyy")``=TEXT(MIN(DATEVALUE(A2:A4)), "mm/dd/yyyy")`
33/1/2023
42/1/2023

Explanation: Here, we have a list of dates in text format in column A. We use the DATEVALUE function to convert these text dates into date serial numbers. Then, we use the MAX function to find the latest date and the MIN function to find the earliest date. The results are formatted as dates in the “mm/dd/yyyy” format.

### Example 13: Using DATEVALUE with IFERROR

Purpose of Example: To handle errors when converting a text string to a date.

Data Tables and Formulas

AB
1Date (Text)Result
2“Not a date”`=IFERROR(DATEVALUE(A2), "Invalid date")`

Explanation: In this example, the text string in column A is not a valid date. We use the DATEVALUE function to attempt to convert this text into a date serial number. Since the conversion will fail, we use the IFERROR function to catch the error and return the “Invalid date.”

These examples demonstrate how the DATEVALUE function can be nested with various other functions to perform data-related tasks. By understanding how to use DATEVALUE with different functions, you can more effectively manipulate and analyze date data in text format.

## Part 3: Tips and Tricks

1. DATEVALUE function is handy when you import data from other sources that may format dates as text.
2. If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year from your computer’s built-in clock. Time information in the date_text argument is ignored.
3. Remember to apply a date format to the cell if you want to view a date serial number as a date.
4. Be aware that your computer’s system date setting may cause the results of a DATEVALUE function to vary.
5. The DATEVALUE function returns the #VALUE! Error value if the value of the date_text argument falls outside of the range between January 1, 1900, and December 31, 9999.