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.
|