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

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

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.

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)

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.