Excel formula to convert date formats

Question

Assuming I have cells that have text values mentioned as:

02/23/15 to 03/01/15

Can I use excel functions to convert this to something like

23-Feb-15 to 1-Mar-15

So far I have looked at formula DATEVALUE but that does not look like solving my specific problem.

I think what I am looking for is: how do I specify a date format (say "dd/mm/yy") for a string (say "02/16/15") and convert it to a date string in another format (say "dd-mmm-yy")

score 2 · Accepted Answer · edited Mar 20 '17 at 10:16

Here's a formula that works for your example no matter what your locale settings are:

=TEXT(DATE(MID(A1,7,2),LEFT(A1,2),MID(A1,4,2)),"dd-mmm-yy") & " to " & TEXT(DATE(RIGHT(A1,2),MID(A1,13,2),MID(A1,16,2)),"dd-mmm-yy")

Here's one that works no matter what's in between the dates so long as the dates are of the format mm/dd/yy and at the very beginning and end:

=TEXT(DATE(MID(A1,7,2),LEFT(A1,2),MID(A1,4,2)),"dd-mmm-yy") & MID(A1,9,LEN(A1)-16) & TEXT(DATE(RIGHT(A1,2),MID(A1,LEN(A1)-7,2),MID(A1,LEN(A1)-4,2)),"dd-mmm-yy")

Depending on your situation, this other option may or may not be helpful. You can change the default date format for the system. That affects Excel's date functions so that they will expect dates to be in that format. Once done, you can use the DATEVALUE function just fine. If this is a one-time operation, I'd suggest changing the format, running the formulas, and changing it back. Otherwise, I'd go with a formula approach. You can get all the details of how to change the system settings here. (I didn't copy and paste it all because it's long.)

score 0 · Answer 2 · edited Jun 12 '20 at 13:48

You could try this (I'm using UK date format)

=CONCATENATE(TEXT(LEFT(A1,8), "dd-mmm-yy")," to ",TEXT(RIGHT(A1,8), "dd-mmm-yy"))

enter image description here

As you can see, it expects the date formats to be a fixed length (8 character, such as mm/dd/yy or /dd/mm/yy).

Since the above is not working, you could try VBa which will read the current value in ColA and paste the new value in ColB

Option Explicit
Sub YikesThereBePirates()
Dim row As Integer
row = 1
Do While Range("A" & row).Value <> ""
Dim splitty() As String
splitty = Split(Range(&quot;A&quot; &amp; row).Value, &quot; to &quot;)

Dim newFirstDate As String
Dim newSecondDate As String

newFirstDate = GetDate(splitty(0))
newSecondDate = GetDate(splitty(1))

Range(&quot;B&quot; &amp; row).Value = newFirstDate &amp; &quot; to &quot; &amp; newSecondDate


row = row + 1
Loop
End Sub
Function GetDate(d As String) As String
Dim splitty() As String
splitty = Split(d, "/")
Dim mnth As String
Select Case (splitty(0))
    Case "01"
    mnth = "Jan"
    Case "02"
    mnth = "Feb"
    Case "03"
    mnth = "Mar"
    Case "04"
    mnth = "Apr"
    Case "05"
    mnth = "May"
    Case "06"
    mnth = "Jun"
    Case "07"
    mnth = "Jul"
    Case "08"
    mnth = "Aug"
    Case "09"
    mnth = "Sep"
    Case "10"
    mnth = "Oct"
    Case "11"
    mnth = "Nov"
    Case "12"
    mnth = "Dec"
  End Select
GetDate = splitty(1) &amp; &quot;-&quot; &amp; mnth &amp; &quot;-&quot; &amp; splitty(2)


End Function

Also, I used US date format too for the VBa to match it to your query.

enter image description here

How do I add VBA in MS Office?

Remember with VBa, take a copy o the file first as a back up (there is no option to undo)

Excel formula to convert date formats

2 Answers2