Forum Discussion
Need Help Handling Different Date Time Formats Imported from Multiple Excel Workbooks
Thanks BA_Pete
I tried both Locale > US and UK and no joy - still those errors unfortunately:
EDIT: I think part of the issue here is that the date format is from two different locales - so when a colleague pulls the NEW exports from his system, he gets it in US format but also with the AM and PM , and for the historical files, they were pulled in the UK so UK format of dd/mm and without the AM PM.
The problem you have here is that many dates can be interpreted as either UK or US format e.g. 6/7/23 or 11/9/23 etc. so you have to be able to tell PQ which conversion it should be using for any given datetime value.
If possible, I think you'll need to identify the exact date at which the locale format changed at source, then create a new custom column to evaluate the conversion required at each date.
Do you have another date column of a known and consistent locale to use as a reference point, like [Report Date] or [Export Data] or similar?
If yes, we can look at the conditional column option.
If no, then I think you'll need to segregate your data import by 'Old Locale' and 'New Locale', perform the relevant conversion on each, then append the two together.
Pete