I have a need to convert dates of format Fri Jun 30 '17, 11:15:56 am to mm/dd/yyyy format using a VBA. I tried format() function but it didn't work.

1 Answers

2
CLR On

If format() doesn't work, then (as you haven't told us otherwise) we have to assume that the cell contains that date as text, not as a date.

In which case, allow me to offer one of many methods of converting this to mm/dd/yyyy:

Format(DateValue(Replace(Mid(Split(Range("A1").Value, ", ")(0), 4, 20), "'", "")), "mm/dd/yyyy")

As @Peh mentions though, this is not the best way to deal with a date. It is better to hold the date as a Date - and if you're writing to a cell, to write that Date to the cell as a Date. This makes manipulation of the date much easier and you just apply formatting to the cell to get the desired look.


To get the Date, you could use:

DateValue(Replace(Mid(Split(Range("A1").Value, ", ")(0), 4, 20), "'", ""))

Explanation:

Split(Range("A1").Value, ", ")(0)

This creates an array that contains the string split in two, the first part (0) contains the left element, the second part (1) contains the right element.

So Fri Jun 30 '17, 11:15:56 am becomes Fri Jun 30 '17

We then take a portion of the left hand part above:

Mid(<left hand part>, 4, 20)

from character 4, for (up to) 20 characters.

So Fri Jun 30 '17 becomes Jun 30 '17

We then remove the ' using a Replace(text , "'", "") function.

So Jun 30 '17 becomes Jun 30 17

The DateValue function converts strings that contain dates into an actual date value. Jun 30 17 is recognisable to Excel as a date and given that the month element is obvious (i.e. a word and not a number) then Excel is able to un-ambiguously convert it.

So Jun 30 17 becomes 42916 (- which is 42,916 days since 31st Dec 1899).