Extract Date From Text using VBA

[hsmith15]

I export data from a sales reporting system to generate custom sales reports.

The date now is exported in the format "Thu Jan 18 22:23:12 UTC 2018".

Is there any way for me to extract the date in format of "mm/dd/yyyy" from this text?

Thanks!

 

[Rick Rothstein]

I export data from a sales reporting system to generate custom sales reports.

The date now is exported in the format "Thu Jan 18 22:23:12 UTC 2018".

Is there any way for me to extract the date in format of "mm/dd/yyyy" from this text?

If your text uses leading zeroes for single digit day and hour numbers, then this might work for you (the formula is dependent on your computer's regional settings)...

=0+(MID(A1,5,5)&","&RIGHT(A1,5))

This formula returns the serial number for the date so you will have to use Cell Formatting to make it look like mm/dd/yyyy.

 

[hsmith15]

Rick,

Thank you very much. I wasn't aware of this formula. The formula you wrote came back as 1/1/18 where the date in the code is 1/18/18.

However, changed it to =0+(MID(A1,5,5)&","&RIGHT(A1,5)). Changing the num_chars in the MID section to 7 instead of 5 came back with 1/18/18. Does this seem like the correct fix?

Thanks.

 

[Rick Rothstein]

Rick,

Thank you very much. I wasn't aware of this formula. The formula you wrote came back as 1/1/18 where the date in the code is 1/18/18.

However, changed it to =0+(MID(A1,5,5)&","&RIGHT(A1,5)). Changing the num_chars in the MID section to 7 instead of 5 came back with 1/18/18. Does this seem like the correct fix?

That second 5 (the one you underlined) was a typo on my part... I meant to type 6 (not 7).

 

[hsmith15]

Sorry, made a typo. See below for the edited formula I used.

=0+(MID(A1,5,7)&","&RIGHT(A1,5))

 

[hsmith15]

Great, I will use 6. Thank you very much!