Extract start date from String Jan14

[rizmomin]

Hello:

I have monthly sheets and have text : Jan14,Feb14,Mar14, etc in cell E2

I need a formula to have 1/1/2014,2/1/2014,3/1/2014 etc in A8 of monthly sheet.

Please let me know of any questions.

Thanks

Riz Momin

[Kaper]

Hi Riz,
Try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[sktneer]

This works for me......

Please Login or Register  to view this content.

[rizmomin]

Hi Sktneer:

The date i am getting is 1/14/2014 instead of 1/1/2014
Please check and let me know
Thnks

Riz

[Pepe Le Mokko]

=DATEVALUE("Jan14") returns 01/01/2014 - Same for the other months

[sktneer]

Pls see the attached sheet.

[rizmomin]

Hi Sktneer:

It is giving 2/14/2014, and needs be the start of the month date : 2/1/2014.
Please confirm.

Thanks

Riz

[Jonmo1]

=DATEVALUE("Jan14") returns 01/01/2014

Not if you have US Date formats in Regional Settings.

Try this (for either US or UK dates)
=DATEVALUE(1&A1)

A1="Jan14"

[Kaper]

=DATEVALUE("Jan14") returns 01/01/2014

Provided one have english version of Excel or right regional settings (as probably rizmomin has)
Unfortunately I do not have it, so have to use such "prothetics" as above.
but of course in my case

Please Login or Register  to view this content.

works fine :-)

[rizmomin]

Hi Jonmo1:

Your solution will fit my requirement.
Thanks a lot for to all for great help.

Riz Momin

[Jonmo1]

You're welcome.