Date Format Issue in Excel after changing . to /
Solved
ClementN
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
Hello everyone,
I'll quickly explain: I'm extracting data from SAP into Excel. The problem is that, by default, SAP writes the dates like this: dd.mm.yyyy.
To counter this issue, I've added a piece of code at the beginning of my macro (thanks to the recorder) that replaces the "." with "/" so I can use the dates.
But now, VBA is changing some dates and converting them to the American format. For example, I start with 08.06.2020 and end up with 06/08/2020. Do you know how to counter this problem?
Thank you in advance, and I wish you all a pleasant day
Configuration: Windows / Chrome 83.0.4103.97
I'll quickly explain: I'm extracting data from SAP into Excel. The problem is that, by default, SAP writes the dates like this: dd.mm.yyyy.
To counter this issue, I've added a piece of code at the beginning of my macro (thanks to the recorder) that replaces the "." with "/" so I can use the dates.
'change . to / in dates
Workbooks("ecart_inventaire.xls").Worksheets(1).Columns("K:K").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
But now, VBA is changing some dates and converting them to the American format. For example, I start with 08.06.2020 and end up with 06/08/2020. Do you know how to counter this problem?
Thank you in advance, and I wish you all a pleasant day
Configuration: Windows / Chrome 83.0.4103.97
2 answers
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention Ambassadeur 1 588
Hello, you don't show us how you import the dates.
I think the simplest way, in VBA, is to use the DateSerial() function, during or after the import. -
I've a macro that automatically executes a SAP transaction and saves the Excel file in the same folder as my macros. So, by default, SAP saves the file with periods (".") in the dates. Therefore, I cannot change the date format during import; they arrive like this.
What’s amusing is that if I manually replace the "." with "/", the date format remains European, but when the macro does it (even though I've recorded exactly the same action), it changes some dates to the American format.
I found a workaround to still manipulate my dates with the ".". I use clng(mid(....)) to extract only the month.
Thank you for your response.
