Hi!
I'm helping out an eBay seller who's using a .csv to calculate his late shipments.
A bit of context: eBay normally gives a report for that with the dates each late shipping took place. However, this report made by eBay has inconsistent formatting rules in the way dates are handled -- some not being even formatted as dates, but as a bunch of text that Excel cannot even interpret as such by changing the formatting.
To create a reusable model (which would work just by copy-pasting the data from the .csv to this file and calculate stuff automatically) I needed to be creative in Excel and create a series of SUBSTITUTE, VLOOKUP, etc. to convert dates like "09 November 2021 CET" to "09/11/2021" (I'm EU-based, so we follow dd/mm/yyyy). To make sure the spaghetti code I created in Excel works, it's important that the column J (which is using a "custom date" format) is formatted as text in the Excel.
This works perfectly okay when I use copy-paste the values from the .csv and use the Convert Text to Table function.
"That's fine", I thought, "I'll just record it as a Macro, so that the seller can do it by themselves".
However, the moment the Macro is recorded (converting again, as text) and I use the key, Excel switches up all months and days. I'm not too sure
Note that I'm based in Europe, so I'm not too sure if changing the region settings of my PC may help.
The Macro is:
I'm helping out an eBay seller who's using a .csv to calculate his late shipments.
A bit of context: eBay normally gives a report for that with the dates each late shipping took place. However, this report made by eBay has inconsistent formatting rules in the way dates are handled -- some not being even formatted as dates, but as a bunch of text that Excel cannot even interpret as such by changing the formatting.
To create a reusable model (which would work just by copy-pasting the data from the .csv to this file and calculate stuff automatically) I needed to be creative in Excel and create a series of SUBSTITUTE, VLOOKUP, etc. to convert dates like "09 November 2021 CET" to "09/11/2021" (I'm EU-based, so we follow dd/mm/yyyy). To make sure the spaghetti code I created in Excel works, it's important that the column J (which is using a "custom date" format) is formatted as text in the Excel.
This works perfectly okay when I use copy-paste the values from the .csv and use the Convert Text to Table function.
"That's fine", I thought, "I'll just record it as a Macro, so that the seller can do it by themselves".
However, the moment the Macro is recorded (converting again, as text) and I use the key, Excel switches up all months and days. I'm not too sure
Note that I'm based in Europe, so I'm not too sure if changing the region settings of my PC may help.
The Macro is:
