troubleshooting Question

Powershell or Excel Macro to standardize US dates before converting to UK dates

Avatar of un_sysadmin11
un_sysadmin11Flag for United States of America asked on
PowershellMicrosoft ExcelMicrosoft Office
9 Comments2 Solutions86 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Rob Henson
Finance Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros