将日期的日期格式更改为字符串时出现问题

问题描述

我正在尝试将 yyyymmdd 的日期类型转换为 ddmmyyy 格式。

• 我尝试使用DATE函数，如下所示：

  
  
  

  = DATE（LEFT（A3; 4）; MID（A3; 5; 3）; RIGHT（A3; 7））

  
  
  

  此功能的原始日期为 20120401 ，但公式返回： 16.12.2104 。 p>




• 我尝试使用YEAR，MONTH和DAY功能，而不是LEFT，MID和RIGHT，但这不是正确的方式。




• 我也尝试使用DATEVALUE，但是由于Excel可能无法识别 yyyymmdd 作为日期，它给了我#VALUE！错误。




• 我已经找到了几个SQL解决方案，但它并不是我的强项 - 而且这在Excel中很容易实现。 / p>

有没有办法在Excel中执行此操作？

解决方案

申请 = DATE（LEFT（A3; 4）; MID（A3; 5; 3）; RIGHT（A3; 7））到20120401在A3有效地重新组装左边的组件字符如下：

这些有效地转换为右侧。

解决方案只是将数字突出显示为红色：

= DATE（LEFT（A3; 4）; MID（A3; 5; 2）; RIGHT（A3; 2））
但是区域设置可能是一个因素，当处理日期序列号和/或似乎是日期，但是一个字符串可能涉及到其他各种问题。

I am currently trying to convert yyyymmdd type of date to a ddmmyyy format.

• I've tried using DATE function and something like this:

  =DATE(LEFT(A3;4);MID(A3;5;3);RIGHT(A3;7))

  Original date for this function is 20120401 but the formula returns: 16.12.2104.

• I've tried using functions YEAR, MONTH and DAY instead of LEFT, MID and RIGHT but that's not the right way.

• I also tried using DATEVALUE but since Excel probably doesn't recognize yyyymmdd as a date, it gives me a #VALUE! error.

• I've found a couple of solutions for SQL but it isn't my strong side yet - and this should be achievable easily in Excel.

Is there a way to do this in Excel?

解决方案

Applying =DATE(LEFT(A3;4);MID(A3;5;3);RIGHT(A3;7)) to 20120401 in A3 is effectively to reassemble the component characters as below on the left:

These effectively become converted as on the right.

The solution was simply to exclude the digits highlighted red with:
=DATE(LEFT(A3;4);MID(A3;5;2);RIGHT(A3;2))but the locale might have been a factor and when dealing with date serial numbers and/or what appears to be a date but is a string various other issues might have been involved.

这篇关于将日期的日期格式更改为字符串时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！