Excel:将美国日期格式转换为欧洲格式 [英] Excel: Convert US date format to European format
问题描述
我具有以下美国日期格式 5/30/2018 8:00:00
.我正在寻找一种优化的公式,无需时间即可将其转换为欧洲格式 30/05/2018
(dd/mm/yyyy).我在网上搜索了一个好的解决方案,却找不到一个简单的解决方案.我正在寻找一个公式,而不是格式化单元格等
I have the following US date format 5/30/2018 8:00:00 AM
. I am looking for an optimized formula to convert it into European format 30/05/2018
(dd/mm/yyyy) without the time. I have searched the net for a good solution, was not able to find an easy one. I am looking for a formula and not to format cells etc
此外,我的计算机设置为欧洲格式,错误地读取了几个月和几天.为了克服这种情况,在excel中进行的任何更改都将有所帮助.
Moreover my computer is set for European format, which reads months and days wrongly. Any changes in excel to overcome this situation would be helpful.
此问题不适用于VBA,但我要求在单元格中输入公式.此外,源单元格未设置为数字/日期格式...它只是常规单元格或自定义单元格
This question is not for VBA but i am requesting a formula to be entered in a cell. Also the source cell is not formatted as a number/date... its just a general cell or custom cell
推荐答案
与此同时,我想出了这个公式
In the meantime, I came up with this formula
= IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,(FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)))))
欢迎使用其他任何优化的公式
Any other optimized formulas are welcome
这篇关于Excel:将美国日期格式转换为欧洲格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!