Converting text into Date Value
Here's a thought:
For text in cell A1... A1: Sep-21-2005 B1: =DATEVALUE(SUBSTITUTE(A1,LEFT(A1,3),MATCH(LEFT(A1, 3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug ","Sep","Oct","Nov","Dec"},0))) You could also set up a vertical list of the 3-letter month names and refer to that, instead. Does that help? *********** Regards, Ron "Shams" wrote: Folks, Dates in a certain file were typed in an informal manner: i.e. Sep-21-2005. Now, since this was entered as a text, Excel is not evaluating it as a Date Format at all (even after I format the cell accordingly). This is what I have done so far: I have used the left, mid and right functions to separate out the variables in 3 distinct columns - Sep (in Col. A), 21 (Col B), 2005 (Col C) What should I do next? My goal is to present the result as 09/21/2005 (Date Format). Any help will be greatly appreciated. Thanks. Shams. |
All times are GMT +1. The time now is 01:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com