Extract Date From Text String

[Nikesh Valji]

Hello all,

I am trying to extract the date from the attached date string. Some of the entries are easy and I can use the "text to column" function. However I think some dates are as text and I am finding it difficult to carry this out for the entire data set. Ideally, I would like to extract the date in the following format: dd-mmm-yy.

Any help would be greatly appreciated.

Thanks,
Nik

 

[sureshsonti]

You can try this in cell B2
=MONTH(A2)&"/" & DAY(A2) & "/" & YEAR(A2)

 

[sureshsonti]

You can try this in cell B2
=MONTH(A2)&"/" & DAY(A2) & "/" & YEAR(A2)

Sorry try this in cell B2

=DAY(A2)& "/" & MONTH(A2) & "/" & YEAR(A2)

 

[Nikesh Valji]

Thanks - I have tried that but it only works for some and others it gives a value error - please see attached.

Thanks,
Nik

 

[Chihiro]

There's mismatch between what's being brought in and what you have set as your region.

How are you importing those dates into your workbook?

I'd recommend importing as text (using import wizard). Then performing text manipulation on it to transform it into dates. Otherwise you'll get mixed results like you got in your workbook.

 

[sureshsonti]

Thanks - I have tried that but it only works for some and others it gives a value error - please see attached.

Thanks,
Nik

Seems to be working for me. See attached file. Just drag the formula down.

 

[Chihiro]

@sureshsonti
It works for you, since your system date format is set to m/dd/yyyy format (as is mine). I suspect Op has it system date (region setting) set to dd/mm/yyyy format.

This causes dates such as 07/31/2018 to be considered invalid date format and imported as text. But dates such as 07/01/2018 will be interpreted as Jan 7, 2018 and imported as such. Hence, why OP's example worked up to 7/12/2018, but not after (as there's no 13th month).

To avoid this sort of mix-up, it is always advisable to either export dates in format required by client application, or in text format, and transform after it is imported to client system using text manipulation.

 

[Haz]

As your dates are padded with 0 you can use absolute references (4, 3)
=TEXT(IF(ISTEXT(A2),MID(A2,4,3)&REPLACE(A2,4,3,),TEXT(A2,"mm/dd/yy")),"dd-mmm-yy")

 

[sureshsonti]

Seems to be working for me. See attached file. Just drag the formula dow

As your dates are padded with 0 you can use absolute references (4, 3)
=TEXT(IF(ISTEXT(A2),MID(A2,4,3)&REPLACE(A2,4,3,),TEXT(A2,"mm/dd/yy")),"dd-mmm-yy")

what do you mean by padded with 0. Thanks

 

[Haz]

That they have a 0 before the numbers smaller than 10, like 01/07/2018 instead of 1/7/2018, so the date always has the same number of digits.

 

[Nikesh Valji]

As your dates are padded with 0 you can use absolute references (4, 3)
=TEXT(IF(ISTEXT(A2),MID(A2,4,3)&REPLACE(A2,4,3,),TEXT(A2,"mm/dd/yy")),"dd-mmm-yy")

Hey Haz

Thanks for the reply above - worked perfectly!

Nik