Converting date formats in Excel

Topic

New Reply

Within Excel 2010, I need to convert dates that are formatted mmddyyyy to ddmmyyyy. Have tried Custom number formats and can’t seem to get anything to work. Ideas anyone?

Thanks,

Ken

Reply | Quote

Replies

I assume these are text string, not real dates.
Do you want to just change the text strings , or convert them to real dates?

If you want to convert the string use a formula

=Mid(CellRef,3,2)&Left(CellRef,2)&Right(CellRef,4)

If You want to convert the string to a real date then use

=Date(Right(CellRef,4),Left(CellRef,2),Mid(CellRef,3,2)) and use a format to show as a date

Where CellRef is the appropriate Cell Reference.

Reply | Quote

Here’s a vba solution, that changes the order within the cells concerned:

Code:

Sub Demo()
Dim oCel As Range, TmpVal As Long
For Each oCel In Selection
  If IsNumeric(oCel.Value) And oCel.HasFormula = False Then
    If Len(oCel.Value) = 8 Then _
      TmpVal = Mid(oCel.Value, 3, 2) & Left(oCel.Value, 2) & Right(oCel.Value, 4)
    If Len(oCel.Value) = 7 Then _
      TmpVal = Mid(oCel.Value, 2, 2) & "0" & Left(oCel.Value, 1) & Right(oCel.Value, 4)
    oCel.Value = TmpVal
  End If
Next
End Sub

I’ve assumed the ‘dates’ are simply numbers, in which case, there’ll be some that are only 7 digits long as the leading 0 won’t be held for months less than 10.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

This sample uses a DATEVALUE formula to convert a text date to a number and then choose a date format that is suitable.

The DATEVALUE formulas are in Column B.

Reply | Quote