Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting data - re-formating data to "date" format
I have raw data that I export from a site for work and the date comes over as
Internationa ISO however it has no dashes in it to help format. Example it looks like this... 20080302 just straight numbers. i need to convert this to US date format. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting data - re-formating data to "date" format
I would think Text to columns.
Ensure you have 2 columns available to the right of your date column. Click Data - Text to Clumns... Select Fixed Width and click between the 4th and 5th charatcers, then between the 6th and 7th characters. Should break it out nicely. If you want to combine back, you can enter a forumla to the right = month cell &"/"& day cell &"/"& year cell. "James" wrote: I have raw data that I export from a site for work and the date comes over as Internationa ISO however it has no dashes in it to help format. Example it looks like this... 20080302 just straight numbers. i need to convert this to US date format. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting data - re-formating data to "date" format
Assuming that the data came across as text, the following formula will
convert the data to an Excel serial date number, changing A1 to the starting cell of your value column/row: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) After copying the formula as far as needed, copy all the cell with a formula and then do an EDIT/PASTE SPECIAL/VALUES to convert the formulas to their respective result value. -- Kevin Backmann "James" wrote: I have raw data that I export from a site for work and the date comes over as Internationa ISO however it has no dashes in it to help format. Example it looks like this... 20080302 just straight numbers. i need to convert this to US date format. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting data - re-formating data to "date" format
Awsome
Kevin yours worked the quickest. thank you "Kevin B" wrote: Assuming that the data came across as text, the following formula will convert the data to an Excel serial date number, changing A1 to the starting cell of your value column/row: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) After copying the formula as far as needed, copy all the cell with a formula and then do an EDIT/PASTE SPECIAL/VALUES to convert the formulas to their respective result value. -- Kevin Backmann "James" wrote: I have raw data that I export from a site for work and the date comes over as Internationa ISO however it has no dashes in it to help format. Example it looks like this... 20080302 just straight numbers. i need to convert this to US date format. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
Excel 2003 - Charts - "Data Range" and Date/Time data on X-Axis | Charts and Charting in Excel | |||
Default display format for "currency" data type | Excel Discussion (Misc queries) | |||
Import HTML data into Excel - data in "1-1" format translates to J | Excel Discussion (Misc queries) | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) |