Forum Discussion
How to standardize different time formats
This was developed on a non-US locale machine so may need some adaption.
ReverseDateλ
= LAMBDA(date,
//"Reverses day and month under the assumption that dates have been imported to Excel using an incorrect locale"
LET(
// "Convert Excel date back to text"
textDate, IF(ISNUMBER(date), TEXT(date, "dd/mm/yyyy hh:mm"), date),
// "Split text date in order to reverse day and month"
splitDate, TEXTSPLIT(textDate,{"/","-","."," "}),
reordered, CHOOSECOLS(splitDate, {2,1,3}),
textTime, TEXTJOIN(" ",, DROP(splitDate, , 3)),
// "Recombine and evaluate"
return, VALUE(TEXTJOIN("/",,reordered) & " " & texttime),
return
)
)
In use, one might have the worksheet formula
= MAP(dateList, ReverseDateλ)
As I understood concrete report could be done in US, could be in non-US locale. Without additional information we can't decide based only on dates shall we revert them or not. Or first to check do we have any texts with columns with dates. If yes to revert and change all dates like 8/4/2024 from April on August.
If so in Power Query it could be much easier.
My preference would be to perform any necessary conversion in PQ rather than introduce erroneous conversions in Excel. Once one is at the point of having suspect dates on the sheet then, as you suggest, examining an individual date may not help. To have a reasonable chance of identifying conversion errors it is the whole dataset that should be examined. The presence of dates with days in the range 13th-31st suggests the import was correct whereas dates remaining as text should be a red flag.
My thought in writing the function is that is that, provided applying it converts an entire dataset to numbers, the resulting dates are most likely valid.