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

un_sysadmin11
un_sysadmin11 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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.

Author

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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.

Author

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?
Finance Analyst
Commented:
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

Author

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 Analyst

Commented:
Thanks for the feedback.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial