Help with Date Format

[chrono2483]

Hello,

In my workbook, I paste in the date in A1 as "Date: 2/9/16" [Feb. 9, 2016]. To extract just the date, I use the formula =RIGHT(A1,7), and it does display 2/9/16, but it recognizes it as Sept 2, 2016. I cannot seem to be able to change it.

Help?

Thank you!

 

[SpillerBD]

Excels interpretation of "2/9/16" is based on your region settings found through your systems Control Panel.

Otherwise you'll need a longer formula that parses that bit of text and evaluates to a date-serial value.

 

[SpillerBD]

While

=DATE(VALUE(RIGHT(A1,2))+2000,VALUE(MID(A1,FIND(":",A1)+1,FIND("/",A1)-1-FIND(":",A1))),FIND("/",A1,FIND("/",A1)+1)-(FIND("/",A1)+1))

should get.. I bet RickXL or someone else might have a far more elegant solution

 

[SpillerBD]

So I had a remembrance that makes the formula method seem soo long and burdensome compared to merely using Text-to-Columns.
Delimited on ":"
Skip first field
set the date to Date Format as "MDY"
You can overwrite or send to new column.

 

[Jonmo1]

I agree the Text To Columns rout is probably the best solution.

But if it must be a formula, here's another method (assuming the dates are all year 2000+)

=DATE(20&RIGHT(A1,2),MID(SUBSTITUTE(A1,"/"," "),7,2),MID(SUBSTITUTE(A1,"/"," "),10,4))

 

[chrono2483]

Thank you SpillerBD and Jonmo1 - both solutions worked, but it did have to be a formula in this scenario but awesome to know of multiple ways to reach the same solution.

 

[ZVI]

The macro solution - just for the collection

Sub SelectionToDate()
' Select cells with text values in date format "mm/dd/yy" and run this macro.
' It converts text values to Excel's date values
  Dim Cell As Range
  Dim a, v
  For Each Cell In Selection
    With Cell
      v = .Value
      If VarType(v) = vbString Then
        If IsDate(v) Then
          a = Split(v, "/")
          If UBound(a) = 2 Then
            .NumberFormat = ""
            .Value = DateSerial(a(2), a(0), a(1))
          Else
            .Select
            MsgBox "Date separator in cell " & .Address(0, 0) & " should be '/' ", vbExclamation, "Exit"
            Exit For
          End If
        End If
      End If
    End With
  Next
End Sub

1. Copy this code
2. Press Alt-F11 to go to VBE
3. Menu: Insert - Module
4. Paste the code
5. Press Alt-Q to close VBE
6. Select range of the cells with text data
7. Press Alt-F8 and run macro SelectionToDate to convert text values in selection to date values