So I have an XLSX file (I would attach it, but it has sensitive data) that has inconsistent US date formatting. The data comes out of salesforce. Some dates are written as 08/30/2018, others are 8/30/2018.
The user that gets this information is in the UK and thus needs to convert these to DD/MM/YYYY. Usually the "Text to column" method would work, but it doesn't. The dates in the cells are text, not numerical.
I think the main problem though is that I need to standardize the US dates with leading zeroes before I can continue to figure out my US>UK date conversion problem.
Looking for a way to do this in powershell, I also assume this will need to be a CSV file now, not xlsx. I tried to create a macro to add the leading zeroes to the dates, but there's alot of wrong dates and it seems like it would take forever to create a proper macro that I can use.