 |
| FREE Excel STUFF |
Search |
| Excel Help. Popular |
| NEW! Multiple Excel Search & Links |
| Excel Formulas |
| Excel Macros |
| Excel Newsletter |
| PRODUCTS |
| Up to $139.00 FREE! |
|
Categories & Search |
| Excel Templates |
| Excel Add-ins |
| Excel Training |
| More.... |
| OTHER |
| Excel Development |
|
|
NEW! More Books.. |
Add Excel Answers & Search To Your Google Toolbar Details |
Current Special! Complete
Excel Excel Training
Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant
Buy/Download, 30 Day Money Back Guarantee
& Free Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
ALSO SEE: Create an Excel Calendar Control | Excel Date and Times
If you work with Excel, sooner or later you will encounter a problem with dates. Particularly if these dates have been imported from another program. Let's look at some of the date formats that you may encounter and also how to convert these to standard Excel dates. For all New Dates I will use the format: mm/dd/yy with a date that is 11-23-03. The cells in column C have been formatted with a format of mm/dd/yy US DATE FORMAT
| A | B | C | |
| 1 | Old Date | Formula Used | New Date |
| 2 | 112303 | =VALUE(LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2)) | 11/23/03 |
| 3 | 031123 | =VALUE(MID(A3,3,2)&"/"&RIGHT(A3,2)&"/"&LEFT(A3,2)) | 11/23/03 |
| 4 | 231103 | =VALUE(MID(A4,3,2)&"/"&LEFT(A4,2)&"/"&RIGHT(A4,2)) | 11/23/03 |
For all New Dates I will use the format: dd/mm/yy with a date that is 23-11-03. The cells in column C have been formatted with a format of dd/mm/yy EUROPEAN DATE FORMAT
| A | B | C | |
| 1 | Old Date | Formula Used | New Date |
| 2 | 112303 | =VALUE(MID(A2,3,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2)) | 23/11/03 |
| 3 | 031123 | =VALUE(RIGHT(A3,2)&"/"&MID(A3,3,2)&"/"&LEFT(A3,2)) | 23/11/03 |
| 4 | 231103 | =VALUE(LEFT(A4,2)&"/"&MID(A4,3,2)&"/"&RIGHT(A4,2)) | 23/11/03 |
If your dates are already in a format of say yy/mm/dd (03/11/23) you can use the Text to Columns feature of Excel. Select the cells that house these dates (must be in one column in continuous rows) then go to Data>Text to Columns. Click Next twice to get to Step 3 of 3 ignoring any settings in steps 1 and 2. Check Date under Column data format and now select YMD from the drop-box
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
