Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearrange values in cell to date
uploaded cell values in column B show as
2009-04-11* (does not read as a date) Need code that will check to see if any value as above exist in column B, if so change order to format that reads as a date. 04/11/2009 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearrange values in cell to date
In what way is it "not a date"... does it have an apostrophe in front of it
(look in the Formula Bar, not the cell, to see it this is the case) or is the cell formatted as Text? -- Rick (MVP - Excel) "J.W. Aldridge" wrote in message ... uploaded cell values in column B show as 2009-04-11 (does not read as a date) Need code that will check to see if any value as above exist in column B, if so change order to format that reads as a date. 04/11/2009 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearrange values in cell to date
Most likely your cells have been formatted as text. Reformat as General.
Now press <F2, and then press <Enter That should change it to dd-mm-yyyy, provided that is how your date format has been set. It might also change it to dd/mm/yyyy, but it would still be a date. -- HTH Kassie Replace xxx with hotmail "J.W. Aldridge" wrote: uploaded cell values in column B show as 2009-04-11 (does not read as a date) Need code that will check to see if any value as above exist in column B, if so change order to format that reads as a date. 04/11/2009 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearrange values in cell to date
kept digging to find out problem...
found out there's a trailing space at the end of 2009-04-11 which prevents it from being read as date or even changed into. I tried several vba codes to remove trailing spaces, but for some reason that doesnt work either. I download the data in its current format so I'm guessing its reading as one object somehow? These numbers are all in one column/string. Any suggestions would be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearrange values in cell to date
You didn't answer the questions I asked you in my other post, but based on
what you have said, I'm guessing the cells are formatted as text. If that is the case, the try this... select your column of dates, click Data/TextToColumns on Excel's menu bar and click the Finish button on the dialog box that appears. Doing this should change any of your "text dates", with or without trailing spaces, to real dates that you can then format as you wish. -- Rick (MVP - Excel) "J.W. Aldridge" wrote in message ... kept digging to find out problem... found out there's a trailing space at the end of 2009-04-11 which prevents it from being read as date or even changed into. I tried several vba codes to remove trailing spaces, but for some reason that doesnt work either. I download the data in its current format so I'm guessing its reading as one object somehow? These numbers are all in one column/string. Any suggestions would be appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearrange values in cell to date
Stay away from the VBA my friend. try =TRIM(Cell reference). However, this
should not prevent it from being seen as a date. It is beginning to sound to me as if you import this data via a csv or txt file. Iow, it is seen as text. If so, save as a .xls, then format the cells as General, <F2 and <Enter on a cell to see what happens. -- HTH Kassie Replace xxx with hotmail "J.W. Aldridge" wrote: kept digging to find out problem... found out there's a trailing space at the end of 2009-04-11 which prevents it from being read as date or even changed into. I tried several vba codes to remove trailing spaces, but for some reason that doesnt work either. I download the data in its current format so I'm guessing its reading as one object somehow? These numbers are all in one column/string. Any suggestions would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rearrange texts and values in new column | Excel Discussion (Misc queries) | |||
Create a formula to rearrange different values in groups | Excel Discussion (Misc queries) | |||
rearrange date | Excel Discussion (Misc queries) | |||
help me on rearrange cells based on its numerical values without repeating any number | Excel Programming | |||
help me on rearrange cells based on its numerical values without repeating any number | Excel Worksheet Functions |