exceldateexcel-dates

Create list of custom non-standard date formats in excel

I have a table in excel with non-standard dates written in four general formats (first column), and would like to convert each to a standard excel date format (2nd column).

Early 2022 should convert to 01/14/2022 YE 2022 should convert to 12/01/2022 2Q 2022 should convert to 05/01/2022 2H 2023 should convert to 09/01/2023

Ideally, I would like to use the custom format table and just add these 4 rules but a formula addressing each of the 4 scenarios would also suffice.

Non-Standard Dates Converted Dates
Early 2022 01/14/2022
YE 2022 12/01/2022
2Q 2022 05/01/2022
2H 2023 09/01/2023

Most likely, nested IF statements will be required that check for the words "Early", "YE", "2Q" and "2H" will be required, followed by amending of last 4 digits.

I've only figured out how to check for one word, but the IF statements need to be nested. =IF(A2="Early", "01/14/", "" ) works, but of course I need to replace the last portion ("") with a new IF statement to look for the next phrase ("YE") and so forth. I also need to amend the last 4 digits.

Solution

  • You could create a table like this

    lookup table

    If that table is in I10:J13 and your estimated dates are in column A, this formula would return the date you want.

    =DATE(MID(A1,FIND(" ",A1)+1,LEN(A1)),MONTH(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),$I$10:$J$13,2,FALSE)),DAY(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),$I$10:$J$13,2,FALSE)))

    The DATE function takes three arguments: year, month, and day. The year is determined by finding a space and assuming everything after the space is the year. The month and day argument find the date in the lookup table and use MONTH() and DAY() to extract those portions. It doesn't matter what year your dates in the lookup table have because this uses the year from the estimated date.