Upload Excel to Excel Online Changing to European

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a after version (Excel 2007 or afterward), this tip may non work for you. For a version of this tip written specifically for later versions of Excel, click here: Converting European Dates to U.s. Dates.

Converting European Dates to U.s. Dates

Linda asked if there is a formula that will convert a date shown in the European manner of twenty-four hours/month/twelvemonth to the U.s. version of month/date/year. Truth be told, this may not exist necessary. You run across, Excel maintains dates, internally, as numeric values and then but displays them using diverse formats. If the dates are truly dates—numeric values—in the worksheet, then you can simply change the format and the dates will be displayed in the way common to the The states.

Of course, the date yous see in a worksheet could be a text value, instead of a engagement value. You can test whether the date is really an Excel engagement or a text value past changing the format of the cell (or cells) to General. (Exercise this using the Number tab of the Format Cells dialog box.) If it is text, you'll see no change in the display. If information technology is a appointment value, the engagement should change to a number that represents the number of days since whatever base appointment your system is using (typically January 1, 1900).

If your dates are truly appointment values, then but change the format of the jail cell (or cells) to whatever date format yous want to utilise. Once again, this is done using the Number tab of the Format Cells dialog box.

If your dates are text values, then you will need to catechumen them to true engagement values (non-text) and then that they can be formatted as only described. You tin do this by using a formula to do the conversion. If you have a text appointment in jail cell A1 in the format dd/mm/yyyy, and so you can utilize the post-obit formula:

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

The result of this formula is a date serial number that is recognized and tin can be formatted past Excel.

Of form, it is possible that y'all have a bunch of mixed dates in your worksheet. Consider the following list of dates:

1/i/11 ii/1/eleven three/1/11 4/1/11 5/one/eleven 6/1/11 seven/1/11 8/1/11 9/1/11 10/ane/11 11/1/11 12/1/11 13/1/xi 14/1/11 15/1/11 sixteen/1/11 17/1/11        

If these are entered into a worksheet, the starting time twelve dates (i/1/xi through 12/ane/eleven) are parsed by Excel as January ane, 2011, through December one, 2011. The adjacent five dates are parsed as text since Excel doesn't, by default, recognize that the dates are in d/grand/y format. If y'all have a bunch of dates like this, yous can rapidly catechumen them to real dates without the use of whatsoever formulas. Just follow these steps:

1. Select all the cells containing the dates—both the appointment values and the text values.
2. Get-go the Text to Columns wizard. (Choose Text to Columns from the Information menu.) (See Figure 1.)



Figure ane. The first screen of the Text to Columns wizard.

3. Choose Fixed Width and click Next to continue to step two of the wizard.
4. If you come across any column break indicators in the dialog box, become rid of them past double-clicking on them. You don't want any such indicators considering you don't desire Excel to retrieve you take static breaking places for your data.
5. When all the cavalcade break indictors are gone, click Next to proceed to pace 3 of the sorcerer.
6. In the Column Data Format section of the dialog, click the Date radio push button.
7. Using the date format drop-downwards listing, choose DMY.
8. Click Finish.

That's it; your information is converted, in place, to the engagement values that Excel tin work with.

ExcelTips is your source for toll-effective Microsoft Excel training. This tip (3309) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and afterwards) here: Converting European Dates to US Dates.

Author Bio

With more than fifty non-fiction books and numerous mag articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

More than FROM ALLEN

Using the System Configuration Utility

Want to change what happens when Windows is started? Information technology'southward piece of cake to make changes if you know how to use the System ...

Detect More

Displaying a Count of Zeros on the Status Bar

Excel allows you to brandish the results of several common worksheet functions on the status bar. The bachelor functions ...

Observe More

Unexpected Error Exporting a PDF

It is common for people to create PDF files from their Give-and-take documents. What can you exercise, though, if your standard way of ...

Discover More

turnertrithere.blogspot.com

Source: http://excel.tips.net/T003309_Converting_European_Dates_to_US_Dates.html

Share this post