Search code examples
exceldate

Formula to format "MM/DD/YYYY" and "M/D/YYYY" strings as date in Excel

I have strings in two formats in the Excel column "MM/DD/YYYY" (04/06/2020 etc.) and "M/D/YYYY" (3/18/2021 etc.).

I would like to change them to date. The below formula works only for strings in the format "MM/DD/YYYY":

=IFERROR(DATE(
RIGHT(P2,4),
LEFT(P2,FIND("/",P2)-1),
MID(P2,FIND("/",P2)+1,FIND("/",P2,FIND("/",P2)+1)-FIND("/",P2)-1)
),DATE(2021,1,1))

How to adjust it to support also "M/D/YYYY" strings?

Solution

  • This worked for me to get expected outout:

    enter image description here

    Notice the dates in column A are left-aligned becaused Excel is reading those values as text but dates in column B are right-aligned because Excel read those values as dates:

    Formula in column B:

    =DATE(RIGHT(A1;4);LEFT(A1;(SEARCH("/";A1))-1);MID(A1;(SEARCH("/";A1))+1;(SEARCH("/";A1;(SEARCH("/";A1))+1))-(SEARCH("/";A1))-1))

    After extracting date, just apply format mm/dd/yyyy to cells and you'll get your desired output