Change text to date format and use if statement to add year.

**ghstrdr60** · 07-31-2014, 05:49 PM

I have some data in the format below and would like to change it to date fromat for calculation purposes:

F15 - Oct
F15 - Feb
F15 - Oct
F15 - April
F15 - Oct
F15 - Nov

I would like to change the text above to date format, however it needs to be 2014 if the months are F15 - May thru December and 2015 if it is F15 January thru April. Thus, F15 Oct should read Oct-14 and F15 - Jan would read Jan - 15. I used the formula =Right(A2, LEN(A2)-3), to remove the F15, however can't seem to combine it with an if statement to create the date as It doesn't work or I get an error.

Thanks.

**Paul** · 07-31-2014, 06:00 PM

The following formula seems to work. It's long because the primary lookup is done twice - first to determine the year based on the month, and second to re-calculate the month for the DATE function. If you put that lookup in its own cell as a helper, the DATE function would be much shorter. (But would use two cells, not one.)

Be sure your month abbreviations are as I have done below. (Or adjust the abbreviations in the formula to match what you're using.) Based on your example, months with up to five letters were spelled out, otherwise they were shortened to three characters.

Finally, format the formula cells as Custom using mmm-yy.

Formula:

Please Login or Register  to view this content.

**ghstrdr60** · 07-31-2014, 06:19 PM

Thanks for the assistance

**AlKey** · 07-31-2014, 07:01 PM

Or this

=VALUE(IF(MONTH(1&REPLACE(A1,1,6,""))<=4,1&REPLACE(A1,1,6,"")&2015,1&REPLACE(A1,1,6,"")&2014))

	A	B
1	F15 - Oct	10/1/2014
2	F15 - Feb	2/1/2015
3	F15 - Oct	10/1/2014
4	F15 - April	4/1/2015
5	F15 - Oct	10/1/2014
6	F15 - Nov	11/1/2014

**Paul** · 07-31-2014, 07:21 PM

Ahh, I've seen that 1&MONTH() before and typically forget to use it!

Good work!

**AlKey** · 07-31-2014, 07:25 PM

Originally Posted by Paul

Ahh, I've seen that 1&MONTH() before and typically forget to use it!

Good work!

Thank you Paul, but why did you give me negative points for???

**Paul** · 07-31-2014, 07:32 PM

I gave you negative points??? Sheesh, must have hit the wrong button. Sorry about that, we'll get it corrected.

**JBeaucaire** · 07-31-2014, 07:51 PM

All fixed.

**AlKey** · 07-31-2014, 07:53 PM

Originally Posted by JBeaucaire

All fixed.

Thank you Jerry

Change text to date format and use if statement to add year.

LinkBack

Thread Tools

Rate This Thread

Display

Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Re: Change text to date format and use if statement to add year.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Plotting Year wise maximum value & occurance date - the input date is in text format.

Excel:How change the textbox format from text to date format?

Keep month and date but change year to current year and reoccur when new year rolls over.

[SOLVED] Use TEXT function as Criteria on Sumifs to convert month/year to date format

change date format into text format.

Bookmarks

Bookmarks

Posting Permissions