Forum Discussion
Change date format.
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
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 FunctionIn the attached, I have placed the code on Module2 for you to test.
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
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.
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