Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Convert Dates To Excel Formatted Dates

| | Information Helpful? Why Not Donate.

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 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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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