I'm trying to import an excel file with date/time data that has a custom excel format applied to show in mmm-yy format. I need the yyyy-mm-dd data.
The Input preview already shows the date in mmm-yy format, so not sure how the parse tool could be used.
Solved! Go to Solution.
The limitation is with .xls file which is old format if you change it to .xlsx it will read it as proper dates.
Workflow:
Hope this helps : )
@messi007 it appears this solution changes the day to the 1st of every month, but the original data provides actual days in the month.
@atcodedog05 this solution works but I was trying to avoid doing anything with the file. My current workaround is to remove the custom formatting and change it to short date, its only slightly longer than opening and saving the file with the format *.xlsx
I ended up using this example to solve the solution without opening the file: I just had to change the xlsb references to xls.
Awesome, thank you for sharing this @someotherguy 🙂