conversion formula
In excel I have about 250 entries with dates shown as this: "Fri Dec 8 12:57:00 CST 2017" I want to change them to this: "8/12/17". What is the formula for doing this?
Assuming your string is in A2, then try this...
In B2
In B2
=DATEVALUE(MID(A2,9,2)&"-"&MID(A2,5,3)&"-"&RIGHT(A2,4))
If you just want the date part, then use this formula, and then format the resulting column as a date format that you want.
»bp
=DATEVALUE(CONCATENATE(TRIM(MID(A1,9,2)),"-",MID(A1,5,3),"-",RIGHT(A1,4)))»bp
@Subodh Tiwari (Neeraj),
We think alike, I'm just 22 seconds slower at typing :).
»bp
We think alike, I'm just 22 seconds slower at typing :).
»bp
You are right Bill! :)
You may call me Neeraj only. :)
You may call me Neeraj only. :)
And if you want to get the time component you could do the following. But that ignores the time zone component, taking that into consideration (assuming they could vary, and not knowing what time zone you want to convert to) would increase complexity substantially...
»bp
=TIMEVALUE(TRIM(MID(A1,11,9)))»bp
ASKER
Sorry I should have been more clear. All i want is Month, date, and year
Okay, thanks, I think you have a solution for that.
»bp
»bp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your efforts in helping me with this little issue. It was a great help to me. I used the last comment and it worked wonderfully .
ASKER
Yes, that is what I am trying to do and it worked fine. thank you very much for helping me out with this
You're welcome Will! Glad it worked as desired.
»bp