Easy way to format

mduntley

Board Regular
Joined
May 23, 2015
Messages
134
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top