Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dat Formatting
I received a large ASCII format document that I imported into Excel. The date
appears as 19500323. Is there anyway to format this to read 03/23/1950. thanks in advance. |
#2
|
|||
|
|||
Mike
you'll probably need an extra column. Assuming the date is in cell A1, the formula would be: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) or, simply: =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) Note that the first formula returns a date *value* which is formatted as a date. The second returns a text string that *looks* like a date. So, the first gives the number 18345 which is then formatted as a date to give 03/23/1950. Regards Trevor "Mike Busch" wrote in message ... I received a large ASCII format document that I imported into Excel. The date appears as 19500323. Is there anyway to format this to read 03/23/1950. thanks in advance. |
#3
|
|||
|
|||
Mike
DataText to ColumnsNextNext. Column Data FormatDateYMD and Finish. Your copied sample came out as desired. Gord Dibben Excel MVP On Fri, 18 Feb 2005 15:21:02 -0800, "Mike Busch" wrote: I received a large ASCII format document that I imported into Excel. The date appears as 19500323. Is there anyway to format this to read 03/23/1950. thanks in advance. |
#4
|
|||
|
|||
One column at a time:
Select that column Data|text to columns choose YMD as the field type Format|cells|number tab and give it the format you like best. Mike Busch wrote: I received a large ASCII format document that I imported into Excel. The date appears as 19500323. Is there anyway to format this to read 03/23/1950. thanks in advance. -- Dave Peterson |
#5
|
|||
|
|||
Ps. If you're importing this file (like File|open), you can choose YMD for that
field and save a little bit of time. Mike Busch wrote: I received a large ASCII format document that I imported into Excel. The date appears as 19500323. Is there anyway to format this to read 03/23/1950. thanks in advance. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting in Excel Help Please..... | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
How do I use conditional formatting for multiple rows? | Excel Worksheet Functions |