OzGrid

Convert Dates To Excel Formatted Dates

< Back to Search results

 Category: [Excel]  Demo Available 

Convert Dates To Excel Formatted Dates

 

Got any Excel/VBA Questions? Free Excel Help

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.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Conditional Formatting
Excel Conditional Line Chart
Excel Consolidation

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


Gallery



stars (0 Reviews)