Easy way to format

mduntley

Board Regular
Joined
May 23, 2015
Messages
67
Hello. I am wondering if there is a VBA i can do to format all of the date in a cell to be the same. This is for multiple row with different dates and not as easy to update.

Example
9/20/2017
5/10/18

<tbody>
</tbody>
what I want
09/20/2017
05/10/2018

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
That format should exist in the format cells dialogue box (ctrl+1). Otherwise you could create a custom format (located under the "custom" category in the Format Cells Dialogue box)

Otherwise you use the text formula to reformat it in a different cell.
=text(A1,"mm/dd/yyyy")

in vba you would do this in a similar manner:
range.NumberFormat = "mm/dd/yyyy"
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Change range as necessary:

Code:
Sub FormatDates()
Dim c As Range, t
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = Split(c, Chr(10))
    If UBound(t) Then c = Format(t(0), "mm/dd/yyyy") & Chr(10) & Format(t(1), "mm/dd/yyyy")
Next
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,308
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Change range as necessary:

Code:
Sub FormatDates()
Dim c As Range, t
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = Split(c, Chr(10))
    If UBound(t) Then c = Format(t(0), "mm/dd/yyyy") & Chr(10) & Format(t(1), "mm/dd/yyyy")
Next
End Sub

If every cell in your range will have two dates per cell, then use the above code that Scott posted. However, if the number of dates per cell can vary, then give this macro a try...
Code:
Sub FixDateFormats()
  Dim X As Long, Cell As Range, DT() As String
  For Each Cell In Selection
    DT = Split(Cell.Value, vbLf)
    For X = 0 To UBound(DT)
      If Len(DT(X)) Then DT(X) = Format(DT(X), "mm/dd/yyyy")
    Next
    Cell.Value = Join(DT, vbLf)
  Next
End Sub
 

mduntley

Board Regular
Joined
May 23, 2015
Messages
67
If every cell in your range will have two dates per cell, then use the above code that Scott posted. However, if the number of dates per cell can vary, then give this macro a try...
Code:
Sub FixDateFormats()
  Dim X As Long, Cell As Range, DT() As String
  For Each Cell In Selection
    DT = Split(Cell.Value, vbLf)
    For X = 0 To UBound(DT)
      If Len(DT(X)) Then DT(X) = Format(DT(X), "mm/dd/yyyy")
    Next
    Cell.Value = Join(DT, vbLf)
  Next
End Sub

I like that, is there a way to make it into a custom formula?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,308
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I like that, is there a way to make it into a custom formula?
I think you are asking for a UDF (user defined function) which can be used in an Excel formula. Here is one, but note that you must turn Wrap Text on for the cell you put that formula in.
Code:
Function FixDateFormat(S As String) As String
  Dim X As Long, DT() As String
  DT = Split(S, vbLf)
  For X = 0 To UBound(DT)
    If Len(DT(X)) Then DT(X) = Format(DT(X), "mm/dd/yyyy")
  Next
  FixDateFormat = Join(DT, vbLf)
End Function
 

mduntley

Board Regular
Joined
May 23, 2015
Messages
67
I think you are asking for a UDF (user defined function) which can be used in an Excel formula. Here is one, but note that you must turn Wrap Text on for the cell you put that formula in.
Code:
Function FixDateFormat(S As String) As String
  Dim X As Long, DT() As String
  DT = Split(S, vbLf)
  For X = 0 To UBound(DT)
    If Len(DT(X)) Then DT(X) = Format(DT(X), "mm/dd/yyyy")
  Next
  FixDateFormat = Join(DT, vbLf)
End Function

Sorry to reply on this topic so far back, but the above formula does not work anymore. I get a #NAME ? when i do this. Can someone help me?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,308
Office Version
  1. 2010
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top