Convert Dates To Excel Formatted Dates

Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

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

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

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

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS