英文:
Excel Dates Stored as Text
问题
我有一系列的日期在Excel中以文本形式存储，格式为D/M/YYYY。我需要将它们转换为日期并格式化为M/D/YYYY。我已经尝试了使用DATEVALUE函数，但不成功。您有什么想法？
英文:
I have a series of dates in excel stored as text like D/M/YYYY. I need them converted to dates and formatted as M/D/YYYY. I have tried datevalue, no go. Thoughts?
答案1
得分: 0
Column A 中是以 dd/mm/yyyy 格式表示的日期，强制为文本值，而非日期。Column B 中的公式只是 =VALUE(A1)，并且将单元格格式设置为 mm/dd/yyyy。
如果这种方法不起作用，您可以使用不同的函数处理字符串：
=DATE(RIGHT(A1,4),MID(A1,SEARCH("/",A1)+1,SEARCH("/",A1,SEARCH("/",A1)+1)-(SEARCH("/",A1)+1)),LEFT(A1,SEARCH("/",A1)-1))。
英文:
Column A are dates in format dd/mm/yyyy forced as text values, not dates. Formula in column B is just =VALUE(A1) and applied cell format as mm/dd/yyyy
If this does not work properly, you need to manipulate the string using different functions:
=DATE(RIGHT(A1,4),MID(A1,SEARCH("/",A1)+1,SEARCH("/",A1,SEARCH("/",A1)+1)-(SEARCH("/",A1)+1)),LEFT(A1,SEARCH("/",A1)-1))
答案2
得分: 0
=DATE(DROP(TEXTSPLIT(A1,"/"),,2),TAKE(DROP(TEXTSPLIT(A1,"/"),,-1),,-1),TAKE(DROP(TEXTSPLIT(A1,"/"),,-1),,1))
或
=DATE(INDEX(TEXTSPLIT(A1,"/"),,3),INDEX(TEXTSPLIT(A1,"/"),,2),INDEX(TEXTSPLIT(A1,"/"),,1))
结果:
不要忘记将C1单元格格式化为所需的日期格式。
英文:
=DATE(DROP(TEXTSPLIT(A1,"/"),,2),TAKE(DROP(TEXTSPLIT(A1,"/"),,-1),,-1),TAKE(DROP(TEXTSPLIT(A1,"/"),,-1),,1))
or
=DATE(INDEX(TEXTSPLIT(A1,"/"),,3),INDEX(TEXTSPLIT(A1,"/"),,2),INDEX(TEXTSPLIT(A1,"/"),,1))
Result:
Don't forget to format C1 cell to your desired date format.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论