Easy way to format

[mduntley]

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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="class: xl45359, width: 112"]9/20/2017
5/10/18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]what I want[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="class: xl45359, width: 112"]09/20/2017
05/10/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ghrain22]

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"

 

[mduntley]

I mean both dates are in the same cell

 

[Scott Huish]

Change range as necessary:

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]

Change range as necessary:

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

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]

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

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]

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.

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]

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.

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]

Sorry to reply on this topic so far back, but the above formula does not work anymore. I get a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL] ? when i do this. Can someone help me?

The #NAME ! error means you use a function name that does not exist in the workbook. Did you spell the function name correctly in your formula? Did you delete the code for the function? If not, what code module is the function in?