excelexcel-formula

How do I change a date in 19950428 to 4/28/1995 in excel?

I have a data as show below in excel with column F having a different date format yyyymmdd as text.

enter image description here

For example 19900216. I need it to be in this format 2/16/1990

Any help I could would be appreciated.

Solution

  • Depends if you want it converting to a date value (so you can base other cell formulas on it) or just as a text string.

    Text String:
    =RIGHT(A1, 2) & "/" & MID(A1, 5, 2) & "/" & LEFT(A1, 4)

    Date Value:
    =DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2)) then change the cell number formatting to get the desired date format.

    (where A1 is the cell to be converted)