How Do I Update A Sentence In Excel To Include Changing Date

The tutorial explains how to use Excel functions to convert text to date and number to date, and how to turn text strings into dates in a non-formula mode. You will likewise acquire how to quickly alter a number to date format.

Since Excel is non the simply application you work with, sometimes yous'll discover yourself working with dates imported in an Excel worksheet from a .csv file or another external source. When that happens, chances are the dates will consign as text entries. Even though they look like dates, Excel won't not recognize them every bit such.

There are many means to catechumen text to engagement in Excel and this tutorial aims to cover them all, so that you tin choose a text-to-date conversion technique most suitable for your information format and your preference for a formula or non-formula way.

• How to spot "text dates" in Excel
• How to convert number to appointment in Excel
• How to convert text to date in Excel
  • DATEVALUE office
  • VALUE function
  • Mathematical operations
  • Converting text strings with custom delimiters
  • Text to Column wizard
  • Paste Special
  • Fixing dates with two-digit years
• How to change text to date in Excel an easy way

How to distinguish normal Excel dates from "text dates"

When importing data into Excel, at that place is often a trouble with date formatting. The imported entries may look similar normal Excel dates to you, but they don't bear like dates. Microsoft Excel treats such entries every bit text, pregnant yous cannot sort your table by date properly, nor can you use those "text dates" in formulas, PivotTables, charts or any other Excel tool that recognizes dates.

There are a few signs that tin can help y'all determine whether a given entry is a date or a text value.

Dates	Text values
Correct-aligned by default. Take Date format in the Number Format box on the Home tab > Number. If several dates are selected, the Status Bar shows Average, Count and SUM.	Left-aligned by default. General format displays in the Number Format box on the Home tab > Number. If several text dates are selected, the Status Bar only shows Count. At that place may be a leading apostrophe visible in the formula bar.

How to convert number to date in Excel

Since all Excel functions that change text to date render a number as a result, permit's have a closer look at converting numbers to dates start.

As you probably know, Excel stores dates and times as series numbers and information technology is but a cell's formatting that forces a number to be displayed every bit a date. For example, 1-Jan-1900 is stored equally number 1, 2-Jan-1900 is stored as 2, and 1-Jan-2015 is stored equally 42005. For more data on how Excel stores dates and times, please see Excel appointment format.

When calculating dates in Excel, the consequence returned by different date functions is oftentimes a series number representing a date. For case, if =TODAY()+vii returns a number similar 44286 instead of the date that is 7 days after today, that does not hateful the formula is wrong. Only, the cell format is set to Full general or Text while information technology should be Date.

To convert such serial number to engagement, all you lot have to do is alter the cell number format. For this, just selection Engagement in the Number Format box on the Home tab.

To use a format other than default, so select the cells with serial numbers and printing Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.

Aye, it's that easy! If you lot want something more sophisticated than predefined Excel date formats, please come across how to create a custom date format in Excel.

If some stubborn number refuses to change to a date, check out Excel date format not working - troubleshooting tips.

How to catechumen 8-digit number to engagement in Excel

It's a very common situation when a date is input as an 8-digit number similar 10032016, and y'all need to convert it into a date value that Excel can recognize (10/03/2016). In this case, but changing the jail cell format to Date won't work - y'all will become ########## as the outcome.

To convert such a number to date, you will have to employ the Engagement function in combination with Correct, LEFT and MID functions. Unfortunately, it is not possible to make a universal formula that volition work in all scenarios because the original number tin can be input in a multifariousness of different formats. For example:

Number	Format	Engagement
10032016	ddmmyyyy	ten-Mar-2016
20160310	yyyymmdd
20161003	yyyyddmm

Anyhow, I volition attempt to explain the full general approach to converting such numbers to dates and provide a few formula examples.

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

=DATE(year, month, day)

And then, what yous need to practice is extract a yr, calendar month and date from the original number and supply them as the corresponding arguments to the Engagement office.

For example, let's come across how you lot tin can catechumen number 10032016 (stored in cell A1) to appointment three/10/2016.

• Extract the year. It'due south the last four digits, then we use the Correct role to pick the terminal 4 characters: RIGHT(A1, 4).
• Extract the month. It'south the three^rd and four^th digits, then nosotros employ the MID office to go them MID(A1, 3, two). Where iii (second statement) is the kickoff number, and ii (tertiary argument) is the number of characters to extract.
• Extract the twenty-four hours. It's the showtime two digits, so we have the LEFT function to return the first ii characters: LEFT(A2,two).

Finally, embed the to a higher place ingredients into the Date office, and you lot go a formula to convert number to date in Excel:

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

The post-obit screenshot demonstrates this and a couple more than formulas in action:

Please pay attention to the terminal formula in the above screenshot (row half-dozen). The original number-engagement (161003) contains only 2 chars representing a year (16). So, to become the year of 2016, we concatenate xx and sixteen using the following formula: xx&LEFT(A6,two). If you don't do this, the Date part will return 1916 by default, which is a bit weird as if Microsoft still lived in the 20^th century :)

Note. The formulas demonstrated in this case work correctly as long as all numbers yous want to convert to dates follow the same pattern.

How to convert text to appointment in Excel

When you spot text dates in your Excel file, nigh likely you would desire to convert those text strings to normal Excel dates so that you tin can refer to them in your formulas to perform various calculations. And every bit is oft the case in Excel, in that location are a few means to tackle the task.

Excel DATEVALUE part - change text to appointment

The DATEVALUE function in Excel converts a engagement in the text format to a serial number that Excel recognizes as a date.

The syntax of Excel's DATEVALUE is very straightforward:

=DATEVALUE(date_text)

So, the formula to catechumen a text value to appointment is as elementary as =DATEVALUE(A1), where A1 is a cell with a date stored as a text string.

Considering the Excel DATEVALUE function converts a text date to a serial number, you will have to make that number look similar a date past applying the Date format to it, as nosotros discussed a moment ago.

The post-obit screenshots demonstrates a few Excel DATEVALUE formulas in action:

Excel DATEVALUE part - things to remember

When converting a text string to a date using the DATEVALUE office, please keep in listen that:

• Time information in text strings is ignored, as you can meet in rows 6 and viii above. To convert text values containing both dates and times, use the VALUE function.
• If the year is omitted in a text date, Excel'southward DATEVALUE will option the current year from your computer's arrangement clock, as demonstrated in row four above.
• Since Microsoft Excel stores dates since January 1, 1900 , the use of the Excel DATEVALUE function on earlier dates will result in the #VALUE! fault.
• The DATEVALUE function cannot catechumen a numeric value to date, nor can it procedure a text cord that looks like a number, for that you will need to utilize the Excel VALUE function, and this is exactly what nosotros are going to talk over adjacent.

Excel VALUE function - catechumen a text string to date

Compared to DATEVALUE, the Excel VALUE function is more versatile. It tin catechumen any text string that looks like a date or number into a number, which you tin easily change to a appointment format of your choosing.

The syntax of the VALUE function is every bit follows:

=VALUE(text)

Where text is a text string or reference to a prison cell containing the text you desire to catechumen to number.

The Excel VALUE function can procedure both appointment and time, the latter is converted to a decimal portion, as you lot can run into in row 6 in the following screenshot:

Mathematical operations to convert text to dates

Apart from using specific Excel functions such as VALUE and DATEVALUE, y'all tin can perform a simple mathematical functioning to force Excel to exercise a text-to-date conversion for you. The required condition is that an operation should not alter the date's value (serial number). Sounds a bit tricky? The following examples volition make things easy!

Assuming that your text date is in cell A1, you tin can use any of the following formulas, so employ the Date format to the prison cell:

• Addition: =A1 + 0
• Multiplication: =A1 * 1
• Segmentation: =A1 / 1
• Double negation: =--A1

As you tin can see in the above screenshot, mathematical operations tin convert dates (rows ii and 4), times (row 6) likewise as numbers formatted equally text (row 8). Sometimes the upshot is even displayed equally a date automatically, and you don't take to bother about changing the prison cell format.

How to convert text strings with custom delimiters to dates

If your text dates contain some delimiter other than a forward slash (/) or dash (-), Excel functions won't exist able to recognize them every bit dates and render the #VALUE! mistake.

To ready this, you can run Excel's Observe and Supersede tool to replace your delimiter with a slash (/), all in one get:

• Select all the text strings you want to convert to dates.
• Press Ctrl+H to open the Discover and Supercede dialog box.
• Enter your custom separator (a dot in this example) in the Find what field, and a slash in the Replace with
• Click the Supplant All

Now, the DATEVALUE or VALUE role should have no trouble with converting the text strings to dates. In the aforementioned style, you can set dates containing any other delimiter, e.g. a infinite or a backward slash.

If you prefer a formula solution, you can use Excel's SUBSTITUTE function instead of Replace All to switch your delimiters to slashes.

Assuming the text strings are in column A, a SUBSTITUTE formula may wait every bit follows:

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

Where A1 is a text appointment and "." is the delimiter your strings are separated with.

Now, let's embed this SUBSTITUTE office into the VALUE formula:

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

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

As you run across, the Excel DATEVALUE and VALUE functions are quite powerful, but both have their limits. For example, if y'all are trying to convert complex text strings like Thursday, January 01, 2015, neither function could help. Luckily, at that place is a non-formula solution that tin can handle this task and the adjacent section explains the detailed steps.

Text to Columns wizard - formula-free way to covert text to appointment

If y'all are a non-formula user type, a long-continuing Excel feature chosen Text To Columns will come in handy. Information technology can cope with uncomplicated text dates demonstrated in Example 1 every bit well as multi-function text strings shown in Case 2.

Example 1. Converting simple text strings to dates

If the text strings you desire to convert to dates look like whatsoever of the following:

• 1.1.2015
• ane.2015
• 01 01 2015
• 2015/1/1

Yous don't actually need formulas, nor exporting or importing anything. All it takes is 5 quick steps.

In this example, we will be converting text strings like 01 01 2015 (day, calendar month and yr are separated with spaces) to dates.

1. In your Excel worksheet, select a cavalcade of text entries you want to convert to dates.
2. Switch to the Data tab, Data Tools group, and click Text to Columns.
   
3. In footstep ane of the Convert Text to Columns Wizard, select Delimited and click Next.
   
4. In stride ii of the magician, uncheck all delimiter boxes and click Next.
   
5. In the last step, select Appointment nether Column data format, cull the format corresponding to your dates, and click Terminate.

In this example, we are converting the text dates formatted every bit "01 02 2015" (month twenty-four hours yr), so we select MDY from the drop down box.

Now, Excel recognizes your text strings as dates, automatically converts them to your default engagement format and displays correct-aligned in the cells. You can change the engagement format in the usual fashion via the Format Cells dialog.

Notation. For the Text to Column sorcerer to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like day/month/yr format while others are calendar month/day/year, yous would get incorrect results.

Case ii. Converting circuitous text strings to dates

If your dates are represented by multi-part text strings, such as:

• Thursday, Jan 01, 2015
• January 01, 2015 three PM

You volition accept to put a bit more than effort and use both the Text to Columns wizard and Excel Appointment function.

1. Select all text strings to be converted to dates.
2. Click the Text to Columns button on the Information tab, Data Tools group.
3. On pace one of the Convert Text to Columns Sorcerer, select Delimited and click Adjacent.
4. On step ii of the wizard, select the delimiters your text strings comprise.

   For example, if you are converting strings separated by commas and spaces, similar "Thursday, January 01, 2015", you should cull both delimiters - Comma and Space.
   

   It also makes sense to select the "Treat consecutive delimiters as ane" option to ignore actress spaces, if your information has any.

   And finally, accept a look at the Information preview window and verify if the text strings are split to columns correctly, then click Next.

5. On step 3 of the wizard, brand sure all columns in the Data Preview section have the General format. If they don't, click on a column and select General under the Column information format options.

   Note. Exercise not choose the Date format for any cavalcade considering each column contains only one component, then Excel won't exist able to sympathise this is a date.

   If you don't need some column, click on it and select Do not import column (skip).

   If yous don't want to overwrite the original information, specify where the columns should exist inserted - enter the accost for the top left cell in the Destination field.

   When washed, click the Terminate button.
   

   Equally yous see in the screenshot higher up, nosotros are skipping the first column with the days of the week, splitting the other information into 3 columns (in the Full general format) and inserting these columns kickoff from cell C2.

   The following screenshot shows the result, with the original information in column A and the split data in columns C, D and East.

   

6. Finally, you accept to combine the date parts together by using a DATE formula. The syntax of the Excel Engagement function is self-explanatory:

   =Date(year, calendar month, mean solar day)

   In our instance, year is in column E and twenty-four hours is in column D, no problem with these.

   It's not and then piece of cake with month considering it is text while the DATE function needs a number. Luckily, Microsoft Excel provides a special Month function that can change a calendar month'south proper name to a calendar month's number:

   =MONTH(serial_number)

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

   =MONTH(1&C2)

   Where C2 contains the proper name of the month, January in our case. "1&" is added to concatenate a date (1 January) and then that the Month function tin convert it to the respective calendar month number.

   And now, let's embed the Month role into the month; statement of our DATE formula:

   =Date(F2,MONTH(i&D2),E2)

And voila, our complex text strings are successfully converted to dates:

Quick conversion of text dates using Paste Special

To apace catechumen a range of simple text strings to dates, y'all tin can use the post-obit fob.

• Copy any empty jail cell (select it and printing Ctrl + C).
• Select the range with text values you desire to convert to dates.
• Right-click the pick, click Paste Special, and select Add in the Paste Special dialog box:
  
• Click OK to complete the conversion and close the dialog.

What you have but done is tell Excel to add a naught (empty jail cell) to your text dates. To exist able to do this, Excel converts a text string to a number, and since adding a zero does not alter the value, you get exactly what y'all wanted - the date's serial number. Equally usual, you lot alter a number to the date format by using the Format Cells dialog.

To learn more than about the Paste Special feature, delight see How to use Paste Special in Excel.

Fixing text dates with two-digit years

The mod versions of Microsoft Excel are smart plenty to spot some obvious errors in your information, or meliorate say, what Excel considers an error. When this happens, you volition run into an error indicator (a small green triangle) in the upper-left corner of the cell and when y'all select the cell, an exclamation mark appears:

Clicking the exclamation mark volition display a few options relevant to your data. In case of a 2-digit year, Excel will inquire if you want to convert it to 19XX or 20XX.

If y'all accept multiple entries of this type, you can prepare them all in one vicious swoop - select all the cells with errors, then click on the exclamation marking and select the appropriate choice.

How to turn on Error Checking in Excel

Normally, Error Checking is enabled in Excel by default. To brand sure, click File > Options > Formulas, scroll down to the Error Checking department and verify if the post-obit options are checked:

• Enable background error checking under Fault Checking;
• Cells containing years represented as ii digits under Mistake checking rules.

How to change text to appointment in Excel an easy style

As you see, converting text to date in Excel is far from being a trivial one-click operation. If you are confused by all unlike use cases and formulas, let me prove you a quick and straightforward manner.

Install our Ultimate Suite (a gratuitous trial version can exist downloaded here), switch to the Ablebits Tools tab (2 new tabs containing 60+ awesome tools will be added to your Excel!) and observe the Text to Appointment push:

To convert text-dates to normal dates, here'south what you do:

1. Select the cells with text strings and click the Text to Date button.
2. Specify the engagement club (days, months and years) in the selected cells.
3. Choose whether to include or non include time in the converted dates.
4. Click Convert.

That's information technology! The results of conversion will appear in the adjacent column, your source data will be preserved. If something goes incorrect, you can merely delete the results and attempt again with a different appointment society.

Tip. If you chose to convert times as well as dates, but the fourth dimension units are missing in the results, be sure to apply a number format that shows both the date and time values. For more info, please see How to create custom appointment and time formats.

If you are curious to larn more about this wonderful tool, please check out its home folio: Text to Appointment for Excel.

This is how you convert text to date in Excel and alter dates to text. Hopefully, you lot have been able to find a technique to your liking. In the next commodity, we will tackle the opposite chore and explore different ways of converting Excel dates to text strings. I thanks for reading and hope to run across y'all next week.

You lot may as well be interested in

Source: https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/

Posted by: robinsonhans1996.blogspot.com

Share this post