Convert a Text Date to a Number Date

Last Updated on 30/04/2024
Reading time: 2 minutes

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.

Format Date and General Format

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)

DATEVALUE convert Text Date to Numerical Date

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

Date format with DATEVALUE function

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

Build the date with 3 cells and text 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

DATEVALUE to convert the cells in Date

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)

Double minus to convert in Excel

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Convert a Text Date to a Number Date

Reading time: 2 minutes
Last Updated on 30/04/2024

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.

Format Date and General Format

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)

DATEVALUE convert Text Date to Numerical Date

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

Date format with DATEVALUE function

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

Build the date with 3 cells and text 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

DATEVALUE to convert the cells in Date

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)

Double minus to convert in Excel

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *