# Excel formula to convert date formats

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")

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.)

• Just created the formula myself. It's same as the first one you posted. Accepting the answer. – Ayusman Mar 19 '15 at 12:37

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"))
``````

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("A" & row).Value, " to ")

Dim newFirstDate As String
Dim newSecondDate As String

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

Range("B" & row).Value = newFirstDate & " to " & 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) & "-" & mnth & "-" & splitty(2)

End Function
``````

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

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)

• TEXT() function is returning the same value 02/23/15 no matter what I do.... what could be wrong? – Prasanna Mar 19 '15 at 11:20
• @Dave this is good. But just like Prasanna mentioned I see 02/23/15. Please note that my column is a "General" format not date. What else can be done? – Ayusman Mar 19 '15 at 11:22
• My entire document is set to use `General` format as well, and it works as shown... Where are you based @Ayusman? There maybe a locale issue. EG, if in Germany, you may have to use tt-mmm-yy (as they have Tag for Day (hence t instead of d)) – Dave Mar 19 '15 at 11:38
• @Prasanna, I don't know, I would guess the issue is the machine's Locale settings. Anyway, the VBa alternative should work – Dave Mar 19 '15 at 12:15