Date Formula

L

Liz J

In column B, I'm looking at data that has dates written in all types of
formats (data entry that was made by many different people). For example,
one person might have written 07/09-08/09 for July 2009-August 2009, while
another person wrote 07/ 09-08/ 09, or another wrote Jul 09-Aug 09. There
are variety of dates, not just July 2009-August 2009.

Is there a formula I could use that would take these different date
combinations and translate months in column C and D and years in column E and
F?
For example,
Column B: 08/09-10/09
Column C: August
Column D: October
Column E: 2009
Column F: 2009

If you could help, I'd really appreciate it! Thank you!
 
L

Luke M

Column C:
=TEXT(DATEVALUE(LEFT(B2,FIND("-",B2)-1)),"mmmm")
Column D:
=TEXT(DATEVALUE(RIGHT(B2,LEN(B2)-FIND("-",B2))),"mmmm")
Column E:
=TEXT(DATEVALUE(LEFT(B2,FIND("-",B2)-1)),"yyyy")
Column F:
=TEXT(DATEVALUE(RIGHT(B2,LEN(B2)-FIND("-",B2))),"yyyy")

Note that all of these return a text string. If you want numbers/values, you
could remove the TEXT function from each formula, and simply custom format
the cells as "mmmm" and "yyyy" (without quotes) respectively.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top