0

I have a column(A) in excel with data like "20141024X000000". First part before x is a date. In another column(B) I want to format the date to become like "14-Oct-24"

What I did was use this function on column B to get the date part and add "/" to separate it to make it look like this "2014/10/24":

    =IF(ISBLANK(A1),"",CONCATENATE(LEFT(A1,4),"/",MID(A1,5,2),"/",MID(A1,7,2)))

I tried using the cell formatter on the column and the cell to convert it to the desired date format but somehow does not work.

I know I can use vlookup with a reference for number and month starts to format it the way i like however I don't want to use a reference.

Is there some other way to make this work or do this without using reference for the months?

Thanks in Advance.

1
  • Your IF statement is backwards. It's telling Excel IF A1 is blank, run the formula, otherwise do nothing. Basically, its a "do nothing" formula. Was that intentional?
    – CharlieRB
    Nov 17, 2014 at 12:33

1 Answer 1

1

The following formula + date formatting worked out for me. You can encapsulate it in your IF statement.

=DATEVALUE(MID(A1,5,2)&"/"&MID(A1,7,2)&"/"&LEFT(A1,4))

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .