## How to use the DATEVALUE function

**What is the DATEVALUE function?**

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.

**What are dates in Excel?**

Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.

For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.

This allows dates to easily be formatted to display in many date/time formatsÂ like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now showsÂ 5/18/1927.

**Why are dates stored as text in Excel?**

Excel tries to identify values as text, numbers, Boolean values, dates, and time automatically but may fail in rare occasions. This may happen when you import data from the internet, databases, text files, and other sources. You can convert the dates to Excel dates if you like but it can be a tedious and time consuming task.

The DATEVALUE function lets you convert the text date in the formula to an Excel date without you first converting them on the worksheet.

**How to enter valid dates in Excel?**

Valid dates are right-aligned in a cell just like numbers, invalid dates are identified as text values and left-aligned. The image above shows that / (forward slash) and - (dash) are valid delimiting characters in Excel dates, demonstrated in cells B2 and B3.

A dot and no delimiters at all, shown in cells B4 and B5, are invalid, these dates are left-aligned by Excel. The DATEVALUE cant properly identify these date values either, you need to change these dates by hand or using the methods described below.

### DATEVALUE function Syntax

DATEVALUE(*date_text*)

### DATEVALUE function Arguments

date_text |
Required. The date stored asÂ text you want to convert to an Excel date (serial number). |

### DATEVALUE function example

A date stored as text can't be sorted, filtered or be used in Excel date calculations. You need to convert the date stored as text to a date that Excel recognizes. The image above shows dates stored as text in cell range B3:B7.

Formula in cell C3:

### Explaining formula

DATEVALUE(B3)

becomes

DATEVALUE("1/4/2013")

and returns

41278 which is the corresponding serial number Excel uses for the date "1/4/2013".

### DATEVALUE function not working

This example shows that the DATEVALUE function can't handle a date with a dot as a delimiting character.

Formula in cell D3:

The DATEVALUE function returns a #VALUE! error.

**How to create an Excel date from dates using dots as delimiting characters?**

The SUBSTITUTE function lets you replace a given text string with a new text string.

Formula in cell D3 that works:

### Explaining formula

#### Step 1 - Substitute dots with forward slash

The SUBSTITUTE function replaces a specific text string in a value. Case sensitive.

Function syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(B3,".","/")

becomes

SUBSTITUTE("1.4.2013",".","/")

and returns

"1/4/2013".

#### Step 2 - Convert text date to an Excel date

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text.

Function syntax: DATEVALUE(date_text)

DATEVALUE(SUBSTITUTE(B3,".","/"))

becomes

DATEVALUE("1/4/2013")

and returns 41278. Here is how to format the cell value as a date:

- Select the cell (D3).
- Press CTRL + 1 to open the "Format Cells.." dialog box.

- Select category : Date
- Pick a date formatting.
- Press with mouse on the "OK" button.

This example shows that the DATEVALUE function can't handle a date without delimiting characters.

Formula in cell D3:

The DATEVALUE function returns a #VALUE! error.

**How to create an Excel date from dates without delimiting characters?**

Formula in cell D3 that works:

### Explaining formula

#### Step 1 - Extract year from date

The RIGHT function extracts a specific number of characters always starting from the right.

Function syntax: RIGHT(text,[num_chars])

RIGHT(B3,4)

becomes

RIGHT("01042013",4)

and returns "2013".

#### Step 2 - Extract month from date

The LEFT function extracts a specific number of characters always starting from the left.

Function syntax: LEFT(text, [num_chars])

LEFT(B3,2)

becomes

LEFT("01042013",2)

and returns "01".

#### Step 3 - Extract day from date

The MID function returns a substring from a string based on theÂ starting position and the number of characters you want to extract.

Function syntax: MID(text, start_num, num_chars)

MID(B3,3,2)

becomes

MID("01042013",3,2)

and returns "04".

#### Step 4 - Convert to an Excel date

The DATE function returns a number that acts as a date in the Excel environment.

Function syntax: DATE(year, month, day)

DATE(RIGHT(B3,4),LEFT(B3,2),MID(B3,3,2))

becomes

DATE("2013","01","04")

and returns 41278.

### 'DATEVALUE' function examples

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

### Functions in 'Date and Time' category

The DATEVALUE function function is one of many functions in the 'Date and Time' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form