Help with Date Format

chrono2483

Board Regular
Joined
Aug 23, 2014
Messages
160
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
While
Code:
=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

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

Board Regular
Joined
Aug 23, 2014
Messages
160
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,836
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
The macro solution - just for the collection :)
Rich (BB code):
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
 
Last edited:

Forum statistics

Threads
1,137,347
Messages
5,680,959
Members
419,946
Latest member
Trickay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top