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?
un_sysadmin11Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Have you tried using the DATEVALUE function to convert the text to a proper date?

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.
un_sysadmin11Author Commented:
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
NorieAnalyst Assistant Commented:
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?
Discover the Answer to Productive IT

Discover app within WatchGuard's Wi-Fi Cloud helps you optimize W-Fi user experience with the most complete set of visibility, troubleshooting, and network health features. Quickly pinpointing network problems will lead to more happy users and most importantly, productive IT.

un_sysadmin11Author Commented:
NorieAnalyst Assistant Commented:
Did that not work?

It works for me though I do have to choose MDY from the date format dropdown not DMY as stated in the article.
un_sysadmin11Author Commented:
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?
Rob HensonFinance AnalystCommented:
With your sample file I have used the text to columns wizard and chose format MDY and the values came through fine. I then formatted as dd/mm/yyyy and the dates show fine.

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. Likewise if someone in the UK selects the MDY option it should convert correctly and then should be able to format as DD/MM/YY in the spreadsheet.

Using dd/mm/yy will also add the leading zeroes. The leading zero is only a visual format with real dates, the true value behind will be an integer representing the number of days since 01 Jan 1900

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
un_sysadmin11Author Commented:
Oh wow, I definitely was overthinking this. I see where I got things wrong.
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.
Rob HensonFinance AnalystCommented:
Thanks for the feedback.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.