Convert to standard date

Looking for help with writing a formula to convert a string to a date. The string presents itself as such in the data I download: Sep-16-2021

Example is attached

Here's one way of converting the date:

=DATE(RIGHT(E2,4),INT((FIND(LEFT(E2,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3)+1,MID(E2,5,2))

Format the result in the style you prefer, then copy down as required.

Hope this helps.

Pete

Thank you!

I get the Year & Day portion of the formula. Can you walk me through how the INT function is used to get the month? Please.


INT((FIND(LEFT(E2,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3)

The FIND function will determine the character where the 3 letters representing the month can be found, so it will return 1 for Jan, 4 for Feb, 7 for Mar, and so on. 1 is subtracted from this number (giving 0, 3, 6 etc. for those months), and then this is divided by 3 to return 0, 1, 2 through the INT function, and then 1 is added back in, so those months will return 1, 2, 3 and so on. That part of the formula thus returns the month number represented by the 3-letter month.

Hope this helps.

Pete

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Formula

Formula:

Please Login or Register  to view this content.

One more formula:=DATE(RIGHT(E2,4),MATCH(LEFT(E2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),MID(E2,5,2))