How to convert a date written with text, like 1 January 2023, to 01/01/2023? With Excel, is very simple and there are 2 ways to do it.
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 year. But 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 minus signs one after the other)
Just with the 2 minus, Excel converts your Text Dates to Numeric Dates
=--($A4&B$3&$B$1)
