Date Formula

  • Thread starter Thread starter Liz J
  • Start date Start date
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!
 
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.
 
Back
Top