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:
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
| A | B | C | |
|---|---|---|---|
| 1 | Date (Text) | Date Serial Number | Result |
| 2 | 1/1/2023 | =DATEVALUE(A2) | 45197 |
| 3 | 2/1/2023 | =DATEVALUE(A3) | 45228 |
| 4 | 3/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
| A | B | C | |
|---|---|---|---|
| 1 | Date (Text) | Date Serial Number | Result |
| 2 | 3/1/2023 | =DATEVALUE(A2) | 45256 |
| 3 | 1/1/2023 | =DATEVALUE(A3) | 45197 |
| 4 | 2/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
| A | B | C | |
|---|---|---|---|
| 1 | Date (Text) | Date Serial Number | Result |
| 2 | 1/1/2023 | =DATEVALUE(A2) | 45197 |
| 3 | 2/1/2023 | =DATEVALUE(A3) | 45228 |
| 4 | 1/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
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Difference in Days | Result |
| 2 | 1/1/2023 | 1/1/2024 | =DATEVALUE(B2)-DATEVALUE(A2) | 365 |
| 3 | 2/1/2023 | 2/1/2024 | =DATEVALUE(B3)-DATEVALUE(A3) | 365 |
| 4 | 3/1/2023 | 3/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
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Today | Future Date | Days Until Future Date | Result |
| 2 | 8/2/2023 | 1/1/2024 | =DATEVALUE(B2)-DATEVALUE(A2) | 152 |
| 3 | 8/2/2023 | 2/1/2024 | =DATEVALUE(B3)-DATEVALUE(A3) | 183 |
| 4 | 8/2/2023 | 3/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
| A | B | |
|---|---|---|
| 1 | Date (Text) | Result |
| 2 | 1/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
| A | B | C | |
|---|---|---|---|
| 1 | Start Date (Text) | End Date (Text) | Total Days |
| 2 | 1/1/2023 | 2/1/2023 | =SUM(DATEVALUE(B2)-DATEVALUE(A2), DATEVALUE(B3)-DATEVALUE(A3)) |
| 3 | 3/1/2023 | 4/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
| A | B | C | |
|---|---|---|---|
| 1 | Date (Serial) | Value | Lookup Date (Text) |
| 2 | 45197 | 100 | 1/1/2023 |
| 3 | 45228 | 200 | |
| 4 | 45256 | 300 | |
| 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
| A | B | C | |
|---|---|---|---|
| 1 | Start Date (Text) | End Date (Text) | Average Days |
| 2 | 1/1/2023 | 2/1/2023 | =AVERAGE(DATEVALUE(B2)-DATEVALUE(A2), DATEVALUE(B3)-DATEVALUE(A3)) |
| 3 | 3/1/2023 | 4/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
| A | B | |
|---|---|---|
| 1 | Date (Text) | Result |
| 2 | 1/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
| A | B | C | |
|---|---|---|---|
| 1 | Date (Text) | Specific Date (Text) | Count |
| 2 | 1/1/2023 | 2/1/2023 | =COUNTIF(A2:A4, "<"&DATEVALUE(B2)) |
| 3 | 1/15/2023 | ||
| 4 | 1/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
| A | B | C | |
|---|---|---|---|
| 1 | Date (Text) | Latest Date | Earliest Date |
| 2 | 1/1/2023 | =TEXT(MAX(DATEVALUE(A2:A4)), "mm/dd/yyyy") | =TEXT(MIN(DATEVALUE(A2:A4)), "mm/dd/yyyy") |
| 3 | 3/1/2023 | ||
| 4 | 2/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
| A | B | |
|---|---|---|
| 1 | Date (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
- DATEVALUE function is handy when you import data from other sources that may format dates as text.
- 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.
- Remember to apply a date format to the cell if you want to view a date serial number as a date.
- Be aware that your computer’s system date setting may cause the results of a DATEVALUE function to vary.
- 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.