Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Imported Date Format
I have imported data from an accounting program and the date format is
20070101 for January 1, 2007. I have looked at various sites that would use Left(),Mid(),Right() to break the numbers and then put them back into 2007/01/01 but was wondering if anyone had a simpler solution. Thanks, Lee Coleman |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Imported Date Format
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Look familiar? "Lee" wrote: I have imported data from an accounting program and the date format is 20070101 for January 1, 2007. I have looked at various sites that would use Left(),Mid(),Right() to break the numbers and then put them back into 2007/01/01 but was wondering if anyone had a simpler solution. Thanks, Lee Coleman |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Imported Date Format
On Feb 16, 4:04 pm, "Lee" wrote:
I have imported data from an accounting program and the date format is 20070101 for January 1, 2007. I have looked at various sites that would use Left(),Mid(),Right() to break the numbers and then put them back into 2007/01/01 but was wondering if anyone had a simpler solution. Thanks, Lee Coleman Highlight the cells. Format cells number tab Select Custom from the dropdown list In the "Type:" box, type in the following:- 0000"/"00"/"00 And this will display it as you wish. Excel wont treat it like a date however, it is just formatted to look like one - the value will still be 20070101. If you do wish to use functions you can try the following:- =LEFT(A1,4)&"/"&MID(A1,5,2)&"/"&RIGHT(A1,2) That is assuming that your date is in A1. Hope this helps, Matt Richardson http://teachr.blogspot.com |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Imported Date Format
Thanks to all for helping.
Lee "Lee" wrote in message ... I have imported data from an accounting program and the date format is 20070101 for January 1, 2007. I have looked at various sites that would use Left(),Mid(),Right() to break the numbers and then put them back into 2007/01/01 but was wondering if anyone had a simpler solution. Thanks, Lee Coleman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text format imported date not wanted | Excel Discussion (Misc queries) | |||
Date format in imported lotus 123 file | New Users to Excel | |||
Imported data changes to date format (5-1-5 changes to May 1, 2005 | Excel Discussion (Misc queries) | |||
unable to change date format on imported data | Excel Discussion (Misc queries) | |||
Imported Data Date Format Problem | Excel Discussion (Misc queries) |