EXCEL中日期转换160831变为2016

众所周知，excel有日期格式，但是我们手里的数据很多时候都是非标准日期格式，而是数字序列比如20160831 ,160831 ,16.08.31等非标准日期字符串，而我们在筛选或者计算的时候，需要标准日期格式2016-08-31这种样式，这样才能方便的进行年月日计算或筛选

示例 2种数据格式筛选对比

其实利用excel中的text和datevalue函数，就可以很方便的将这几种格式处理成标准格式

基本原理：text函数将数字用-变换为日期样式，注意这一步处理后看起来显示的结果就是日期，但并不能进行日期运算及筛选，所以还需要再用datevalue将日期格式字符串变换为日期数值，处理后的结果会看到一个6位数字，这就是日期数值，最后再将日期数值列右键单元格格式设置为日期格式即可，如果只需要保留结果，就把处理完的数据复制粘贴为数值，再粘贴回原位即可

1. 20160831，这种格式离标准日期只差一点点，公式如下，整列下拉复制公式

   =DATEVALUE(TEXT(A3,'0000-00-00')
   
   
   

   
   
   

2. 160831,和上面一个一样，区别在于差了一个20，公式如下
   =DATEVALUE(TEXT(C2,'00-00-00'))
   
   
   

   
   
   

3. 16.08.31，这种格式就需要用到另外一个函数substitute来把点替换掉，再datevalue转换为日期，公式如下
   =DATEVALUE(SUBSTITUTE(E2,'.','-'))

如果你自己动手测试一下，就会发现不加20上面的160831直接用datevalue（TEXT(C2,'00-00-00')），结果也是正确的，这是因为2000年纪元的系统自动识别，如果是1999年以下的，那么前缀必须要自己补齐了才能正确识别