英文:
Badly formatted dates
问题
6/20/2023
6/27/2023
6/5/2023
6/24/2023
6/28/2023
6/6/2023
6/22/2023
6/5/2023
6/29/2023
6/12/2023
英文:
Someone sends me a report on Google Sheets with a column of bad dates every month. Every month I struggle to make them consistent. The whole table has hundreds of rows, so doing it manually is a big ball ache. I want DD/MM/YYYY on an Excel sheet.
I have tried downloading to Excel, copying and pasting, and text to columns, and still haven't found a quick solution.
6/20/2023
6/27/2023
6/5/2023
6/24/2023
6/28/2023
6/6/2023
6/22/2023
6/5/2023
6/29/2023
6/12/2023
答案1
得分: 2
你可以尝试使用一个辅助列（然后如果需要，复制/粘贴值）将其作为文本处理并转换为日期：
=LET(tx, TO_TEXT(A1), DATE(REGEXEXTRACT(tx, "/.+/(\d*)"), REGEXEXTRACT(tx, "(\d*)/"), REGEXEXTRACT(tx, ".*/(.*)/")))
作为数组的方式：
=MAP(A1:A, LAMBDA(each, IF(each="", "", LET(tx, TO_TEXT(each), DATE(REGEXEXTRACT(tx, "/.+/(\d*)"), REGEXEXTRACT(tx, "(\d*)/"), REGEXEXTRACT(tx, ".*/(.*)/"))))))
英文:
You can try to work with a helper column (and then copy/paste-values if needed) that works with it as a text and converts it to date:
=LET(tx,TO_TEXT(A1),
DATE(REGEXEXTRACT(tx,"/.+/(\d*)"),REGEXEXTRACT(tx,"(\d*)/"),REGEXEXTRACT(tx,".*/(.*)/")))
And as an array:
=MAP(A1:A,LAMBDA(each,IF(each="","",
LET(tx,TO_TEXT(each),
DATE(REGEXEXTRACT(tx,"/.+/(\d*)"),REGEXEXTRACT(tx,"(\d*)/"),REGEXEXTRACT(tx,".*/(.*)/"))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论