Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have date like this:
Date |
June-20-2018 |
Jun-19-09 |
01-12-2020 |
20-02-2020 |
How do I convert them in a common format? say dd-mm-yyyy.
Use the Alt() function
=Date(Alt(Num(Date#([Date],'MMM-DD-YYYY')),Num(Date#([Date],'MMM-DD-YY')),Num(Date#([Date],'MM-DD-YYYY')),Num(Date#([Date],'DD-MM-YYYY'))),'DD-MM-YYYY')
the suggestion by @jwjackso will work in many cases, but not if your column could contain both 'MM-DD-YYYY' and 'DD-MM-YYYY' as you can by no means know you just looking at eg. 06-12-2021 identify the format. It will return a date value using any of the two patterns. It could be 12th of June or the 6th of December, you can't tell which.