Microsoft Excel’s DATEVALUE function is a highly helpful tool for creating dates in several formats and converting dates written as texts into dates. This lesson explains the DATEVALUE function and shows us how to use it correctly.
This Content Covers:
- Definition of DATEVALUE Function.
- Syntax, Arguments and Purpose of DATEVALUE Function.
- How to use DATEVALUE Function in Excel?
- By Inserting a Date in the Function
- By using Cell References in the Function
- Cell Formatting for DATEVALUE Function with Format Cells Dialogue Box.
- Definition of DATEVALUE Function.
The DATEVALUE function in Excel is an in-built function which converts a date that is represented as a text string into a genuine Excel date. Dates imported from external sources that are in text format can be properly formatted and recognized by Excel using the DATEVALUE function for further analysis. The DATEVALUE Function is listed under Excel Date/Time functions.
- Syntax, Arguments and Purpose of DATEVALUE Function.
Syntax:
=DATEVALUE(date_text)
Purpose:
Transform a textual date into a real date
Arguments:
date_text- A valid date in text format.
- How to use DATEVALUE Function in Excel?
3.1 By Inserting a Date in the Function
Numerous date formats can be used when directly entering dates into the DATEVALUE function.
Step 1: Select a cell, enter a date with your preferred date format inside the DATEVALUE function, and hit Enter key.
Step 2: Each time you enter a different date into a cell with a different date format, that cell will receive serial number for the dates.
Step 3: To give all the numerical dates an appropriate date format appearance, select them all and then select Long Date or Short Date.
3.2 By using Cell References in the Function
We will be converting these dates from Data column into legitimate dates using the DATEVALUE function.
Step 1: Select cell B2 and insert the formula given below inside it and click Enter key.
=DATEVALUE(A2)
Step 2: When you hit Enter key, the formula will return a numeric value inside the cell which is the date’s serial number. Now right click on the lower right corner of cell B2 and drag it down to B6.
Step 3: Here you can see cell B6 resulted in #VALUE error. This happened because the date in A6 was not formatted as text. If you format it as text then the formula will return a number value just like the rest of them.
Step 4: Select the number values and choose Short Date or Long Date from this drop-down list.
- Cell Formatting for DATEVALUE Function with Format Cells Dialogue Box.
Suppose you have created some numeric date values using any of the above-shown methods and you don’t want to use the Long Date and Short Date to format them. Instead, you want to choose a custom date format. You can do that with the help of Format Cells dialogue box.
Step 1: Select the cells and right click on them. Choose Format Cells. Or select the cells and use CTRL+1 shortcut to open Format Cells dialogue box.
Step 2: Go to Number Tab>>Date and choose your preferred date format. Click OK.
Step 3: All the cells containing DATEVALUE formula have been formatted into the selected date format.
For ready-to-use Dashboard Templates:
