Change date format.

[larsgunnar007]

Hi.

I struggle getting the date format change.

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.

I use this command in a macro where i mark all dates in a row and change the date format as i explained abowe.

Can someone help?

[#2]

@larsgunnar007 

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 --> Text to Columns --> click on Finish in the next step.

This would convert the date text string into a real date and then you can change the formatting of the date without an issue.

[#3]

@larsgunnar007 

Hi again and thanks for your answer

from;

Subodh_Tiwari_sktneer (Valued Contributor) mentioned you in a post! Join the conversation below:

First of all the common =ISNUMBER(A1), in Norwegian ERNUM (A1), returns #NAVN? probably (#NAME?), Not true or False.

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

So I'm still stuck to return that date format from yyyy.mm.dd to dd.mm.yyyy still.

Is there any more solutions to change multiply date cells?

The best from

Lars Gunnar

[#4]

@larsgunnar007 

Can you please upload a sample workbook to see how your data is organized on the sheet?

You actually didn't reply the following question.

What date separator do you use as per your regional settings? Is it a dot (period)?

[#5]

@Subodh_Tiwari_sktneer 

Of course.

I mark the Row green where I want do change the datoformat all-in-one operation. An put it in the attached macro.

Yes I separate with dot (.)

ExchangeConverter.xlsm Attached.

I'm exited to hear your solution.

Sincerely

Lars Gunnar

[#6]

@larsgunnar007 

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.

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.

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 Function

In the attached, I have placed the code on Module2 for you to test.

[#7]

Hello, let assume you want to change the date format from mm/dd/yyyy to dd/mm/yyyy or dd.mm.yyyy.

Yo can make the permanent change in the Control Panel of your system. To do that, kindly follow the steps below
1. Type in Control Panel
2. Under Clock and Region, click on change date, time or number formats
3. Click on Additional settings
4. Click on Date tab
5. In the Short date under Date Format, specify the date format you want i.e. dd/mm/yyyy
6. Click OK.

The above steps will permanently take effect on your system including on Excel

[#8]

@Subodh_Tiwari_sktneer 

Hi again.

I think this is a kind of bug or fault in Excel.

I followed your advice looked into the CP the short format who was like this dd.MM.YYYY and long like dddd d. MMMM YYYY.

I tried to change to small letters like dd.mm.yyyy and dddd d.mmmm yyyy but W10 returned "one or several of the letters you tried are invalid..." ..so i had to use the letters as shown above. That is also the format I want.

I also tried other formats but still the format return original from the imported source.

What I can say is that here must MS make a change in Excel or make a command who clean Cells from invisible characters without without deleting the cell content. I'm convinced that it is these characters who make the challenge here.

When I google this, i can see that someone import the data into Notebook and restore it in Excel. That's not a good solution I thing. So I beg you to come up with something who "walk around" this fault in Excel.

Thank you so far.

Sincerely

Lars Gunnar

[#9]

@larsgunnar007 

Did you try the code in the file I uploaded in my last post?

If not, download and save that file.

Now open the file, enable the editing when prompted and then press Alt+F8 to open Macros window, select the macro ConvertToDates and hit Run and let me know if the code works as desired.

Also, open a new blank workbook and input a valid date in the cell A1 and a formula =ISNUMBER(A1) in the cell B1 and share the screenshot of both the cells to see what's your valid date format and what does the formula in B1 returns.

[#10]

@Subodh_Tiwari_sktneer 

Hi again Subodh. I have been occupied but now I have tested and can comment.

MICROSOFT CHALLENGE

I really stay on my opinion that changing the date format should reply the date in the columns marked. Not doing a lot of strange commands to make i append. If you mark and change the number-format in a cell or -severals, it change the cell contents promptly! This should happens in changing date formats too. I want you to report this challenge in to the developers of Excel an give me a feedback when that happens.

ISNUMBER

Well, i tried your "ISNUMBER" command. If i I put the date 25.10.1960 into A1 i get True in B1. If i tried to write 1960.10.25 the cell reply 25.10.1960. If i 1st time put 1960.10.25 the cell, it returns 1960.10.25 and B1 says true. If I try 25.10.60 the cell return 1960.10.25. And B1 return true. So I think the 1st written date in a cell stays with that format. And if i hange the format i tht cell it didn't return to the  the new chosen format. And that's the problem here.

MACROS

So- ..I looked into your Macro. It was really cool I think. I do not program Macros on that level. My way are to try using the record function and try to learn that way. When I tried your Macro it worked well. The challenge was that the macro use a lot of time to go among all the date cells marked. It seems to add [CR] cell by sell from G24 to GMA24 who is all currency dates in the worksheet shown to you. Actually I also tried to mark from G24 ro the right end of the sheet. The reason why, is that i want to have that mark fixed in stead of mark manually. When I go to the end I secure all the incremented dates who new imported sheets will have from the Norwegian Bank.

NOTEPAD VARIANT

I also tried to mark all date columns, changing the date format to dd.mm.yyyy, copy them to NotePad and paste back again to excel. Problem solved! If I want to program this in a macro it stopped for me after starting NotePad. I didnt find any command to view Notpad and put the cursor into the opened NotePad Sheet. I could not find any command to close Notepad either. So maybe you can help here?

FINALLY

It's Definitively not getting rid of rubbish-formatting in the cell who case this problem i think. Therefore must there be a command who delete all formatting before setting the wanted format.

Waiting 4 Your reply.

Sincerely

Lars Gunnar Nesseth