# Convert a Text Date to a Number Date

Last Updated on 06/09/2023

How to convert a date written with text, like 1 January 2023, to a number date 01/01/2023? With Excel, is very simple and there are 2 ways to do it.

1. DATEVALUE function

This function has been created just for this action

2. The symbol -- (2 hyphen)

This short writing converts a text date to a number date

## Origin of the problem

A date is a numerical value. In Excel, a date is the number of days that separate 01/01/1900 (the first date of Excel), from any date.

For instance, let's take the date of 02/04/2023, and let's apply the General format. The result is 45018.  45018 means the number of days after 01/01/1900.

Now, if your dates have text inside (like Jan, Feb, Mar, ..), Excel interprets the cell's contents. And there you lose all the benefits of working on dates, like:

• Loss of date's hierarchy with the filters
• Unable to select a specific year or a month
• No calculations possible; Weekends or public holidays
• ...

## Methods to Convert a Text Date to a Numeric Date

2 methods in Excel allow you to convert Text Dates to Numeric Dates

• With the DATEVALUE function
• Using an Excel-specific writing trick

### Using the DATEVALUE function

The DATEVALUE function is a function that converts a Text Date into a Numeric Date. Just specify as a parameter the cell containing the date in Text, and the function takes care of the rest.

=DATEVALUE(A2)

Then, you must apply the Date Format to see the date in your cell.

But we can go much further in the design of dates by combining several cells.

For example, we have the day, month, and year in 3 different cells. We build a date using the symbol & for concatenating the three cells.

=\$A4&B\$3&\$B\$1

It is interesting to note that with this writing, there is no space between day, month, and yearBut this is not a problem for the DATEVALUE function.

=DATEVALUE(\$A4&B\$3&\$B\$1)

Then, the use of dollars is to simplify the copying of the formula in the other cells

### TIP: Write Easily to Convert in Excel

In Excel, there is an advanced technique to convert your data with -- (2 hyphen signs one after the other)

Just with the 2 minus, Excel converts your Text Dates to Numeric Dates

=--(\$A4&B\$3&\$B\$1)

# Convert a Text Date to a Number Date

Last Updated on 06/09/2023

How to convert a date written with text, like 1 January 2023, to a number date 01/01/2023? With Excel, is very simple and there are 2 ways to do it.

1. DATEVALUE function

This function has been created just for this action

2. The symbol -- (2 hyphen)

This short writing converts a text date to a number date

## Origin of the problem

A date is a numerical value. In Excel, a date is the number of days that separate 01/01/1900 (the first date of Excel), from any date.

For instance, let's take the date of 02/04/2023, and let's apply the General format. The result is 45018.  45018 means the number of days after 01/01/1900.

Now, if your dates have text inside (like Jan, Feb, Mar, ..), Excel interprets the cell's contents. And there you lose all the benefits of working on dates, like:

• Loss of date's hierarchy with the filters
• Unable to select a specific year or a month
• No calculations possible; Weekends or public holidays
• ...

## Methods to Convert a Text Date to a Numeric Date

2 methods in Excel allow you to convert Text Dates to Numeric Dates

• With the DATEVALUE function
• Using an Excel-specific writing trick

### Using the DATEVALUE function

The DATEVALUE function is a function that converts a Text Date into a Numeric Date. Just specify as a parameter the cell containing the date in Text, and the function takes care of the rest.

=DATEVALUE(A2)

Then, you must apply the Date Format to see the date in your cell.

But we can go much further in the design of dates by combining several cells.

For example, we have the day, month, and year in 3 different cells. We build a date using the symbol & for concatenating the three cells.

=\$A4&B\$3&\$B\$1

It is interesting to note that with this writing, there is no space between day, month, and yearBut this is not a problem for the DATEVALUE function.

=DATEVALUE(\$A4&B\$3&\$B\$1)

Then, the use of dollars is to simplify the copying of the formula in the other cells

### TIP: Write Easily to Convert in Excel

In Excel, there is an advanced technique to convert your data with -- (2 hyphen signs one after the other)

Just with the 2 minus, Excel converts your Text Dates to Numeric Dates

=--(\$A4&B\$3&\$B\$1)