Change date format.

Question

Hi.&nbsp;I struggle getting the date format change.&nbsp;If there stay 2020.03.20 in A1 anf i mark A1, and change the format from yyyy.mm.dd to dd.mm.yyy A1 do not change. Still it stay 2020.03.20 in stead for 20.03.2020.&nbsp;I use this command in a macro where i mark all dates in a row and change the date format as i explained abowe.&nbsp;Can someone help?

subodh_tiwari_sktneer · Answer

larsgunnar007&nbsp;If changing the date format in A1 doesn't change the display format of the date in A1 that means the date in A1 looks like a date but in fact it is a date string.Remember that Excel treats dates as real numbers and to check whether the date in A1 is a real date or not, put the formula =ISNUMBER(A1) in a blank cell. If the formula returns a True that means the A1 contains a real date but if the formula returns False, A1 contains a text string which looks like a date but not a real date.What date separator do you use as per your regional settings? Is it a dot (period)?If yes, select the date in A1, go to Data Tab --&gt; Text to Columns --&gt; click on Finish in the next step.&nbsp;This would convert the date text string into a real date and then you can change the formatting of the date without an issue.

larsgunnar007 · Answer

larsgunnar007&nbsp;Hi again and thanks for your answerfrom;Subodh_Tiwari_sktneer (Valued Contributor) mentioned you in a post! Join the conversation below:&nbsp;First of all the common =ISNUMBER(A1), in Norwegian ERNUM (A1), returns #NAVN? probably (#NAME?), Not true or False.&nbsp;When I tried the Function "Text to Colums" it work only when i tried a singel cell. With multiply cells.. -Excel returns "the function do not work in multiply cells".&nbsp;&nbsp;So I'm still stuck to return that date format from yyyy.mm.dd to dd.mm.yyyy still.&nbsp;Is there any more solutions to change multiply date cells?&nbsp;The best fromLars Gunnar&nbsp;

subodh_tiwari_sktneer · Answer

larsgunnar007&nbsp;Can you please upload a sample workbook to see how your data is organized on the sheet?&nbsp;You actually didn't reply the following question.What date separator do you use as per your regional settings? Is it a dot (period)?

larsgunnar007 · Answer

Subodh_Tiwari_sktneer&nbsp;Of course.&nbsp;I mark the Row green where I want do change the datoformat all-in-one operation. An put it in the attached macro.&nbsp;Yes I separate with dot (.)&nbsp;ExchangeConverter.xlsm Attached.&nbsp;I'm exited to hear your solution.&nbsp;SincerelyLars Gunnar&nbsp;

subodh_tiwari_sktneer · Answer

larsgunnar007&nbsp;&nbsp;Please give the following code a try which may or may not work as dates are tricky to handle.My default date format is mm/dd/yyyy and changed it to mm.dd.yyyy to test the code for you and it worked on my end but not sure how this would work on your end.&nbsp;Insert a New Module say Module2 and place the codes given below on to Module2 and run the code ConvertToDates. The code will prompt you to select the Date cells and you should select the green cells with dates and press OK to continue.After code is run, let me know if the code converts all the dates into the real dates.&nbsp;Dim dtFound As Boolean

Sub ConvertToDates()
Dim Rng As Range
Dim Cel As Range

On Error Resume Next
Set Rng = Application.InputBox("Please select the Cells with Date in them.", "Convert Date String Into Real Date!", Type:=8)
On Error GoTo 0

Application.ScreenUpdating = False

If Rng Is Nothing Then
    MsgBox "You didn't select the Date Cells!", vbExclamation
    Exit Sub
End If

For Each Cel In Rng
    Cel.Value = DateStringToDate(Cel.Text)
    If dtFound Then
        Cel.TextToColumns Destination:=Cel
        dtFound = False
    End If
Next Cel
Rng.NumberFormat = "dd.mm.yyyy"
Application.ScreenUpdating = True
End Sub

Function DateStringToDate(ByVal str As String)
Dim Matches As Object
Dim y       As Long
Dim m       As Long
Dim d       As Long
With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "(\d{4})\.(\d{2})\.(\d{2})"
    If .Test(str) Then
        dtFound = True
        Set Matches = .Execute(str)
        y = Matches(0).SubMatches(0)
        m = Matches(0).SubMatches(1)
        d = Matches(0).SubMatches(2)
        DateStringToDate = DateSerial(y, m, d)
    Else
        DateStringToDate = str
    End If
End With
End FunctionIn the attached, I have placed the code on Module2 for you to test.&nbsp;&nbsp;

Forum Discussion

Change date format.

9 Replies

Resources