Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dates in Excel - yyyymmdd, not formatted, how do I format this?
I have a column of dates, they are in yyyymmdd format (no slashes or
anything) and they're text. I cannot format them, how do I do this? Basically, I want to change 19700429 to 4/29/1970, but for a whole column of different dates (dates not in sequential order). Thanks in advance!!!!! |
#2
|
|||
|
|||
Insert a helper column. Then, assuming your "19700429" is in cell A1, paste
the following formula into B1. =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Copy the formula down and you'll be all set. Then if you want to get rid of the helper column, copy the values in column B, and paste values on top of column A. "colin" wrote: I have a column of dates, they are in yyyymmdd format (no slashes or anything) and they're text. I cannot format them, how do I do this? Basically, I want to change 19700429 to 4/29/1970, but for a whole column of different dates (dates not in sequential order). Thanks in advance!!!!! |
#3
|
|||
|
|||
That worked perfet, thanks Jonathan!
"Jonathan Cooper" wrote: Insert a helper column. Then, assuming your "19700429" is in cell A1, paste the following formula into B1. =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Copy the formula down and you'll be all set. Then if you want to get rid of the helper column, copy the values in column B, and paste values on top of column A. "colin" wrote: I have a column of dates, they are in yyyymmdd format (no slashes or anything) and they're text. I cannot format them, how do I do this? Basically, I want to change 19700429 to 4/29/1970, but for a whole column of different dates (dates not in sequential order). Thanks in advance!!!!! |
#4
|
|||
|
|||
On Thu, 29 Sep 2005 08:52:03 -0700, "colin"
wrote: I have a column of dates, they are in yyyymmdd format (no slashes or anything) and they're text. I cannot format them, how do I do this? Basically, I want to change 19700429 to 4/29/1970, but for a whole column of different dates (dates not in sequential order). Thanks in advance!!!!! Two ways: Select the column Data/Text to Columns Next Next Column Data Format/Date: YMD Finish -------------- or you can use a formula in a "helper" column =--TEXT(A1,"0000\/00\/00") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates in Excel linked with Outlook Calendar | Excel Worksheet Functions | |||
Why won't it Excel fill in weekly dates now, changes them to daily | Excel Worksheet Functions | |||
how do I select unique dates from a database in Excel? | Excel Worksheet Functions | |||
Ensure dates imported into Excel 2003 are shown in DD/MM/YYY form. | Excel Discussion (Misc queries) | |||
dates in Excel 2003 | Excel Discussion (Misc queries) |