How to convert text to Date and number to Date in Microsoft Excel?

Microsoft Excel is more than just the application with which we are working. Sometimes we work with the various dates that are effectively imported in a Microsoft Excel worksheet from a .csv file or another external source. When this happens, the dates will be exported as text entries, and even though they look similar to the dates, Microsoft Excel won't recognize them as such.

In Microsoft Excel, various ways can be used to convert the given text to a particular date in an Excel sheet effectively, and this tutorial will aim to cover them all so that we can choose a text-to-date conversion technique most suitably for our data format and then our preference for a formula or for the non-formula way respectively.

1. How can one easily spot "text dates" in Microsoft Excel?
2. How can one convert the given number to the Date in Microsoft Excel?
3. How can one easily convert the text to a date in Microsoft Excel?
   1. What do you mean by the "DATEVALUE" Function?
   2. VALUE Function.
   3. Use of Mathematical operations.
   4. Effective conversation of the text strings with the custom delimiters.
   5. Conversation of the Text to a column Wizard.
4. How can one change the respective text to Date in Microsoft Excel in the easiest manner?

How one can easily distinguish normal Excel dates from "text dates" in Microsoft Excel?

It was known that, while importing a particular amount of data into Microsoft Excel, there is often a problem with date formatting. And the imported entries may look much similar to the normal Excel dates to us effectively, but they do not behave like dates as usual.

Moreover, Microsoft Excel treats all such entries as text, which means we cannot properly sort our table by Date; neither can we use those "text dates" in formulas, pivot tables, charts, or any other Microsoft Excel tool that primarily recognizes dates as well.

Few of the signs help us to determine whether a given entry is a date or a text value, which are as follows:

How can one convert numbers to dates in Microsoft Excel?

It was well known that all the available Microsoft Excel functions that change the text to Date will be returning a number; as a result, now let us have a closer look at converting numbers to dates first as well.

The Microsoft Excel will be storing the dates and times as serial numbers, and only a cell formatting forces a number to be displayed as a date.

• For example, 1-Jan-1900 is the Date that can be effectively stored as number 1, 2-Jan-1900 is the other Date that can be stored as 2, and 1-Jan-2015 is stored as 42005.

When we move on calculating dates in Microsoft Excel, the result is returned by different date functions available in Microsoft Excel. A date function is often a serial number representing a date.

• For example: if =TODAY()+7 returns get a number like 44286 instead of the Date that is 7 days just after today, that does not mean that the entered formula is wrong. , the given respective cell format is set to the General or Text while it should also be Date

We must change the cell number format to convert such serial numbers to the Date effectively. So for this, we will pick out the Date in the Number Format box on the Hometab.

And to apply a given format other than the default, we will be selecting the cells with the help of the serial numbers, and after that, we will be pressing Ctrl+1 to open the Format Cells dialog. On the Number tab, we will choose the Date, select the desired date format under the Type, and click on the OK button.

How can we convert an 8-digit number to Date in Microsoft Excel

In Microsoft Excel, a very common situation existed when a particular date was input as an 8-digit number, such as 10032016. We need to convert this into a date value that Excel can efficiently recognize as (10/03/2016).

Moreover, we can change the cell format to Date in this case. That won't work - and we will be getting ########## as a result respectively.

And in order to convert such a number to Date, we will be then making use of the DATE Function in combination with the RIGHT, LEFT, and MID functions. Unfortunately, this is only possible to make a universal formula, so that will be working in some scenarios because the original number can be input in various formats.

• For example:

We will explain the general approach to converting such numbers to dates and then provide a few formulas as examples.

For the starters, we will remember the order of the Microsoft Excel Date function arguments:

DATE (year, month, and day)

So, we need to extract a year, month, and Date from the original number, and we will supply them as the corresponding arguments to the Date function.

• For example: Let us now see how we can convert the number as 10032016 (which is stored in cell A1) to Date 3/10/2016.

1. We are extracting the year. It is the last 4 digits, so we use the RIGHT functions to pick the last 4 characters: RIGHT (A1, 4).
2. They are extracting the month. It is the 3^rd as well as the 4^th digits, so we can employ the MID function to get them MID (A1, 3, 2), in which all the 3 (second argument) is the start number, and 2 (third argument) is the number of characters to extract it effectively.
3. Extracting of the day. It is the first 2 digits, so we need the LEFT function to return the first 2 characters as LEFT (A2, 2).

Finally, we will be embedding all the above ingredients into the respective Date function, and we will be getting a formula for converting out the number to Date in an Excel sheet as well:

=DATE (RIGHT (A1, 4), MID (A1, 3, 2), LEFT (A1, 2))

And the following screenshot usually demonstrates this and a couple more formulas in action effectively.

We must pay attention to the last formula in the above screenshot (row 6). And the given original number-date (161003) primarily contains only 2 chars representing a year (16). So, to get 2016, we will be concatenating 20 and 16 by just using the following formula: 20&LEFT(A6,2). If we do not do this, the Date function will return 1916 by default.

How can one convert text to Date in Microsoft Excel?

Whenever we spot text dates in our separate Microsoft Excel files, we would want to convert those text strings into a normal Excel date to easily refer to each of them in our formulas to perform various calculations. And as is often the case in Microsoft Excel, a few ways can be used to tackle the task effectively.

Microsoft Excel DATEVALUE function that can be used to change the text to the particular Date

The DATEVALUE function in Microsoft Excel converts a date in text format to a serial number that can be recognized as a date by Microsoft Excel.

Syntax:

The syntax of the Microsoft Excel DATEVALUE is represented as follows:

DATEVALUE (date_text)

So the above-given formula that can be effectively used to convert a text value to Date is as simple as =DATEVALUE (A1), where A1 is a cell containing a date stored as a text string respectively.

It is because the Microsoft Excel DATEVALUE function primarily converts a text date to a serial number, and we have to make that number look like a date by just applying the DATE format to it.

Microsoft Excel VALUE function - used for converting a text string to a Date

Compared with the DATEVALUE, the Microsoft Excel VALUE function is very versatile. It can convert any text string that looks exactly like a date or a number into a number, which we can easily change to a date format of our choice.

And the syntax of the VALUE function, which can be used in Microsoft Excel, is as follows:

The text is a text string or reference to a cell containing the text we need to convert into a number.

Moreover, the Microsoft Excel VALUE function can efficiently process out both the Date as well as the time, and later on, this is converted to a decimal portion, as can be seen in row 6 in the below screenshot:

Use of the mathematical operations for converting text to dates

Apart from using the specific Type of Microsoft Excel functions such as VALUE and DATEVALUE, we can easily perform a simple mathematical operation to force Excel to do a text-to-date conversion for us. And the required condition for this to be achieved is that a procedure should not change the date value (serial number).

Assuming that our text date is in cell A1, and we can make use of the following formulas, after that, we will apply the Date format to the respective cell:

Addition: =A1 + 0

Multiplication: =A1 * 1

Division: =A1 / 1

Double negation: =--A1

As per the above screenshot, the mathematical operations can convert dates (rows 2 and 4), times (row 6) as well as numbers that are formatted as text (row 8). Sometimes the result is even displayed as a date automatically.

How can we convert text strings with custom delimiters to dates?

If our text dates contain some delimiter other than a forward slash (/) or a dash (-), Microsoft Excel functions would not be able to recognize them as dates and then return the #VALUE! Error.

And to fix this, we can run Microsoft Excel's Find and Replace tool to replace our delimiter with a slash (/).

1. First, we will select all the text strings we want to convert to dates.
2. After that, we will press Ctrl+H to open the Find and Replace dialog boxes.
3. And then we will be entering our custom separator) in the Find what field, and a slash in the Replace with.
4. After completing the above steps, we will click "Replace All".

The particular DATEVALUE function should have no problem converting the text strings into dates. And in the same manner, we can fix out dates while containing any other delimiter, e.g., a space.

And if we prefer a formula solution, we can use the Microsoft Excels SUBSTITUTE function instead of Replace All to switch our delimiters to slashes.

Assuming that the text strings are in column A, a SUBSTITUTE formula may look as follows:

=SUBSTITUTE (A1, ".", "/")

In which A1 is a text date and "." is the delimiter our strings are separated with now

Let us embed this SUBSTITUTE function into the VALUE formula:

=VALUE (SUBSTITUTE (A1, ".", "/"))

And have the text strings converted to dates, all with a single formula respectively.

It was seen that the Microsoft Excel DATEVALUE and the VALUE functions are very powerful, but both have their limits.

• For example: If in case we are trying to convert complex text strings, such as Thursday, January 01, 2015, then neither function could help us in this in any way. And luckily, in Microsoft Excel, there are non-formula solutions that can handle this task. This can be explained in the next sections.

Text to Columns wizard

In Microsoft Excel, if we are a non-formula user type, then in that case, a long-standing Excel feature known as Text To Columns will come in handy, and it can cope with various simple text dates, which are demonstrated in Example 1 and also in the multi-part text strings as shown in Example 2;

Example 1: Conversion of the simple text strings to dates

If the text strings which we want to convert to dates may look like any of the following:

1.1.2015

1.2015

01 01 2015

2015/1/1

And we don't need these formulas, nor exporting or importing anything. All it takes is 5 quick steps which are as follows:

In this example, we will effectively convert text strings like 01 01 2015 (day, month, and year separated with spaces) to dates.

Step 1: In our Excel worksheet, we will select a column of the text entries we want to convert to dates.

Step 2: After that, we will switch to the Data tab, Data Tools group, and then click the Text to Columns options.

Step 3: In step 1 of the Convert Text to Columns Wizard, we will select the Delimited and click next.

Step 4: Now, in step 2 of the wizard, we will uncheck all delimiter boxes and then click on the Next option respectively.

Step 5: Now, in the final step, we will then select the Date which is present just under the Column data format, and choosing the format corresponding to our dates, and click on the Finish button.

And in this respective example, we are converting the particular text dates formatted as "01 02 2015" (month day year), so we will be selecting the MDY from the drop-down box.

Microsoft Excel will then recognizes our text strings as dates, and automatically will then convert them to our default date format, and displays them right-aligned in the cells respectively.

Example 2: Conversion of the complex text strings to dates

If in case our dates are represented by multi-part text strings, such as:

Thursday, January 01, 2015

January 01, 2015, 3 PM

Then we have to put more effort and using both the Text to Columns wizard and the Excel DATE functions.

Step 1: First, we will select all text strings that need to be converted to dates.

Step 2: We will then click the Text to Columns button on the Data tab, Data Tools group.

Step 3: On step 1 of the Convert Text to Columns Wizard, we will select the Delimited and click Next.

Step 4: In step 2 of the wizard, we will select the delimiters our text strings contain.

• For example: If we are converting strings separated by commas and spaces, like "Thursday, January 01, 2015", we should choose both delimiters - Comma and Space, respectively.

And it also makes sense to select the "Treat consecutive delimiters as one" option to ignore extra spaces if our particular data has any of them in it.

Finally, look at the Data preview window and verify if the text strings are split into columns correctly. After then we will be clicking on the Next option as well.

Step 5: On step 3 of the wizard, ensure all columns in the Data Preview section have the General format. If they don't, we need to click on a column and select General under the Column data format options.

And if we don't need some column, then click on it and select the Do not import column (skip).

And if in case we don't want to overwrite the original data, specify where the columns should be inserted - and enter the address for the top left cell in the Destination field.

When done, we will click on the Finish button respectively.

As it was seen in the screenshot above, we are just skipping the first column with the days of the week, and splitting the other data into 3 columns (in the General format), after that we will be then inserting these columns that is beginning from cell C2.

The following screenshot shows the result, with the original data in column A, and then we will be splitting the data in columns C, D, and E as well.

Finally, we must combine the Date parts using a DATE formula. And the syntax of the Excel DATE function is self-explanatory:

DATE (year, month, and day)

In our case, the year is in column E, and the day is in column D; no problem.

It is much difficult with month because it is in the form of the text, while the DATE function usually needs a number. Luckily, Microsoft Excel provides a special MONTH function that can change a month's name to a month's number:

=MONTH(serial_number)

For the MONTH function to understand it deals with a date, we put it like this:

=MONTH(1&C2)

Where C2 contains the name of the month, January in our case. "1&" is added to concatenate a date (January 01) so that the MONTH function can convert it to the corresponding month number.

And now, let's embed the MONTH function into the month; argument of our DATE formula:

=DATE(F2,MONTH(1&D2),E2)