excel年月日格式转换|excel表格中日期格式如何设置转换

excel年月日格式转换|excel表格中日期格式如何设置转换）

编按：哈喽，大家好！相信大多数人在刚接触excel时，在excel中录入日期的格式都是类似于“xxxx.xx.xx”的形式，以“.”作为年月日的分隔。但是这样的日期却不符合excel日期的填写规范，是错误的日期。今天我们就来认识下excel中日期的填写规范以及怎样将错误的日期格式改为正确的日期格式，赶紧来看看吧！

＊＊＊＊＊＊＊＊＊

小丽：“苗老师，你说我输入的这个日期要怎么变成带有年月日的日期呀？”

苗老师：“修改一下日期格式就好了。”

小丽：“不行呀，我刚试过了，改了还是原来的样子。”

苗老师：“表发我看看。”

小丽：“好！”，如下图所示：

苗老师：“哦，你这个格式不是日期格式呀，Excel是不认的，改格式自然也无从谈起。”

小丽：“那怎么办呢？”

苗老师：“你把‘.’批量替换成‘/’或者是‘-’，就行啦。”如下图所示：

修改完后，就可以改成带有年月日的日期格式了。如下图所示：

小丽：“为什么你写的日期就可以任意改格式，而我写的却不能？”

苗老师：“这里就要介绍一下Excel里的真日期和假日期了。”

我把Excel里可以任意修改日期格式，并且可运算的日期叫做真日期，其他的叫做假日期。例如下面都是常见的输入日期的方式，这里面有的是真日期，有的是假日期。

很多人就喜欢用“2019.1.1”这样的格式，虽然符合国人的填写习惯，却不符合Excel日期的填写规范。想要把假日期“2019.1.1”变为真日期，除了可以使用上述说到的查找替换解决，还可以使用SUBSTITUTE函数实现。

公式：=SUBSTITUTE(A1,".","-")，如下图所示：

那么类似于“20190101”这样的假日期，怎样才能变为真日期呢？我们可以使用函数来解决。

公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

别看这么长一串公式就觉得很复杂，主要就是两个部分，DATE是一个日期格式的函数。我们先用LEFT，MID，RIGHT三个函数分别提取出假日期中的年月日，再分别带入到DATE函数的year、month、day参数中即可。如下图所示：

这里再举一个关于格式问题的例子。如图7所示，下面的日期虽然看上去是真日期，但实际上却是不可计算、无法任意改变格式的文本格式。我们这里用ISTEXT函数来检测它，这个函数是用来判断单元格中的值是否为文本，文本则显示“TRUE”，不是文本则显示“FALSE”。

当然，这里我们也可以取消单元格居中的显示方式，通过excel中“文本靠左，数字靠右”这一定律，来判断单元格中数据的格式。|注意：日期时间是一种特殊的数值，可以直接参与计算）

苗老师：“那怎么才能将上述这种情况修改为正确的日期格式呢？”

小丽：“直接把格式改成日期不就好了吗？”

苗老师：“那是不行的，将Excel中的文本格式修改成数值或是日期格式，需要挨个双击单元格后才能生效。所以我们要用另一种办法来强制改变单元格格式，那就是excel中的分列功能。”

分列的本来作用是将一列数据使用某个符号或是以某个位置进行分列，如下图所示：

不知道大家有没注意到GIF9中最后点确定的那个界面，就是我们用来修改格式的界面，如下图所示：

我们可以不设置前面分列的内容，直接点击“下一步”跳到最后一个页面，将格式改为日期，就可以得到正确的日期格式了。如下图所示：

这里还有一种情况要说明一下，前面说到日期时间是一种特殊的数值，可以直接参与计算。其实在Excel里每个日期都有其对应的数字，如果我们把真日期的格式改为常规或是数值，就会发现日期全变成了数字。如下图所示：

所以当你发现日期变成了一串数字时，千万别紧张，只要把格式修改成日期格式就可以变回来了。

“小丽你明白了吗？”

小丽：“苗老师，你说了这么多，我都听懂了，可是我们经理就喜欢看‘2019.1.1’这样的日期，有没有办法既能保证运算，又能保留点格式的日期呢？”

苗老师：“我说了一大堆又绕回来了。好吧，这也不难，设置excel中的‘自定义格式’就能搞定。”

我们平时使用的格式，无非是常规 、日期、文本、百分比等等。如下图所示：

“自定义格式”用的比较少，不过它的作用可一点儿都不小。我们可以使用它制定很多想要的格式，在这里我就介绍一下怎么使用它制定需要的日期格式。比如我们要设置“2019.1.1”这样的格式，只需要在“自定义格式”里输入“yyyy.m.d”就行了。其中y代表年，4个y就表示4位数的年，m表示月，d表示日，然后中间用点号隔开。如下图所示：

这样一来，数据在单元格中显示的内容就是我们需要的格式了，但在编辑栏中显示的仍然是我们原本输入的正确的日期格式。

总结：日期格式是我们在日常工作中经常打交道的一个格式，如何用好它，在工作中也是非常重要的，判断日期格式，修改格式等等都是常见的问题，小伙伴们学会了吗？