EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

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 special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / 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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates