Skip to content

How to Change Text Format to Date in Excel? – Useful Formulas

9 de March de 2022

Excel is one of the most used office software by companies to control their finances and the flow of their inventories. Mainly identified by its spreadsheet where you can perform amounts of mathematical operations. The use of Excel has gone beyond the previously exclusive business sphere to be placed in institutions with different objectives such as education, health centers and the home.

With it we can organize income and expenses in our home or educational planning, schedules of activities, charts and graphs for statistical calculation. By downloading the Excel program you can access these and other of its multiple functions.

What procedure should be followed to change the format of text to date in Excel?

Many times when we import date data from different sources into our files there is no match between the formats. Therefore, we need to convert those texts to date format.

Many times these data have blank spaces at the beginning or end of the data. Dates may contain non-standard characters to separate the data or that they use a different order or it is simply a format not recognized by Excel. There are different procedures so that we can have a unified format.

manually

Writing the date in the cell you require is a very simple way but not the most recommended. It is advantageous when there is little data that you want to registerry if it is common to several cells you just have to use the copy and paste function. To do this, copy the date in one of the cells and then click with the right mouse button on the date in question. Select the “copy” option and place the pointer in the cell where you want to paste the data. Click on that cell and select “paste”.

using formulas in excel to format text to date

Using Excel Formulas

You can use various Excel formulas to set the date format. The large number of calculations that you perform in Excel need to be registered with their corresponding date to be able to control and organize them. Excel has the “date” function which is a default tool. In other words, the records you make will be made in principle under that internal criteria.

What formulas do you need to change text to date format in Excel?

Excel also has the “date” formula that you can use for your data to be saved in a universal format that you can use in the different calculations of your worksheet. For this write the formula “=date (year; month; day). You must remember that the year must consist of four digits so as not to confuse it with another century. For example, if you type “20” Excel will not recognize if it is 1920 or 2020.

You can combine date data from multiple cells. When these data are in different cells you can use the “cell references” function. Enter the formula “=DATE” and copy the cell ranges you want to merge. For example “=DATE (D1; D2: D3). You can count cells with a common word such as year, day or month and thus easily detect the elements that you will use to combine the cells.

You can choose between default date formats and edit them to create your own format. For this go to the “category” option and select “custom”. In the “type” text box type the format code and click ok. You can write any text as long as you put it in quotes so that Excel interprets it as codes. So you can use hyphens, commas, bars or lines or any other character.

Using the formula ‘= DATEVALUE ( date_text )’

Excel is configured to format text to look like a date, even if it is written in words or numbers. We can verify if this information has acquired a valid format, observe if the content is justified to the right. If so, the format has been recognized as valid. If it is left justified, Excel has not recognized it as a date and treats it as text.

You can also use the “date number” function. With it you convert a text into a serial number to represent a valid date. In some cases when Excel cannot convert that value you must select the cell where the error “# VALUE!” appears. and select a format from the start list.

using value date formula

With the formula ‘=VALUE(text)’

This formula allows you to change the appearance of a number. For this you must apply the “number codes” format. This gives you a more readable format that can be combined with text or symbols. There are a number of very common combinations that can be copied directly from Excel that you can try.

For example, to calculate the age with the date of birth you can use the combination “=TEXT(TODAY(),’DD/MM/YY’)”. This formula gives today’s date information in the format day, month, year. By combining them with a date of birth you can calculate the age of a person or event.