Powershell or Excel Macro to standardize US dates before converting to UK dates
Hello,
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.
Thoughts?
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.
Thoughts?
ASKER
No, I haven't tried the DATEVALUE function yet. I will look that up and test.
The goal is for the file to remain as a XLSX file, not CSV. (I just thought if I were to use powershell, it would have to be a CSV) I uploaded a sample of the dates, if you'd like to try.
Closed_Date.xlsx
The goal is for the file to remain as a XLSX file, not CSV. (I just thought if I were to use powershell, it would have to be a CSV) I uploaded a sample of the dates, if you'd like to try.
Closed_Date.xlsx
I can't see how Text to columns wouldn't work even with the leading zero inconsistency and the values being text.
How exactly where you trying it?
How exactly where you trying it?
ASKER
Using this method: https://theexceltrainer.co.uk/converting-date-format-using-text-to-columns/
EXPERT CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was using the DMY format to match UK settings. But that doesn't fix the missing leading zero problem. Does choosing MDY add the leading zeroes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh wow, I definitely was overthinking this. I see where I got things wrong.
That fixes the leading zero problem! Thank you.
The DMY/MDY entry in the text to columns refers to the source format not the format required in the output.
So if your source data is in MDY format (US format) then you need to select that option.
That fixes the leading zero problem! Thank you.
Thanks for the feedback.
Once it is a proper date, the value behind the cell should be correct in UK and US formats.
If the file to be transferred to the UK is an excel file, there should be no problem.
However if it going to be a CSV file then there could be an issue. The recipient in the UK should be able to use the text to columns function and convert the date using the MDY input option in the text to columns wizard.
If you can upload a sample list of dates and no other data, I can see what happens at this end as I am in the UK.