芮和
分享更多关于Office的知识

Excel日期数据的“########” 该如何解决

日期是Excel中出现频率非常高的数据类型,无论是合同、工资条、资产折旧中随处可见,那么Excel中日期数据有哪些处理的注意点,有哪些表达形式呢?为什么设置的日期经常变成数字或者“########”,今天我们就来深究下Excel中的日期数据类型。

Hi,大家好,这里是芮和。

首先来了解下在Excel中,最标准的日期是数字格式,具体长什么样子的呢?例如“43572”、“43788”,如下:

很多同学在操作Excel的时候,一看到日期是这种数字,就开始不知所措了,其实这个是Excel最标准的日期,那么为什么是这种存储格式呢?其实也非常容易理解。

在Excel中,日期是存储以“1900/1/0”为起点进行计算的:

  1. 1900/1/1”比这个日期大1天,则存储为1
  2. 2019/06/08”比这个日期大43624天,则存储为43624

碰到这种日期转换成我们可识别日期的方法也非常简单,只需将「单元格格式」设置成「日期」即可。

既然Excel是以1900/1/0为起点计算存储日期的,那么Excel是不是能存储这个时间之前的日期呢?答案是否定的,在Excel中如果直接将一个负数转换成日期,Excel会变成一大长串「#」的乱码:

既然我们不能通过「负数」的方式来转换日期,那么手动输入「起点日期」之前的日期是否可以呢?我们可以来尝试下:

大家观察下,我们可以手动输入「1800/1/1」,但是Excel也不识别,可以看到Excel将起点日期之前的数据变成了「左对齐」,正常的日期是「右对齐」,Excel中左对齐的数据通常为文本类型。

也就意味着「1800/1/1」被存储为「文本格式」,并没有「日期」的可计算的特性;而「1995/10/10」录入为「标准日期」,是支持「计算」的。

讲解完日期的存储规范,那么我们来看下日期的一些常用格式设置,选中日期,按「Ctrl+1」可以快速打开单元格设置,在「日期」格式中,我们可以看到非常多的「设置格式」。

例如最常见的“2019/6/8”、“2019年6月8日”等。如果这里的日期格式不符合你的要求,我们可以通过自定义单元格来进行设置。

标准日期可以设置横杆或者斜杆分割的日期

自定义单元格格式设置为「yyyy-m-d」或者「yyyy/m/d」可以将日期展示为「2019/6/8」或者「2019-6-8」格式。

y代表年,m代表月,d代表日,经常也会看见「yyyy/mm/dd」这种格式,其实2个m表示将不足2位的月份以0进行补充,同理d也相同。例如「2019/6/8」变成「2019/06/08」。

这里的4个y还可以用一个字母e进行代替,例如:

yyyy/mm/dd」还可以写成「e/mm/dd

当然这里的“-”或者“/”可以换成“.”,默认以“/”为标准符号

8位数字的非标准日期处理方式

在Excel中,更常见的是8位数字表示的日期,例如「20190608」这种格式,刚刚我们讲过如果存储为这种数字格式,那么Excel将会以「1900/1/0」为起点加上20190608天,所以如果我们直接将这个数设置成「日期格式」,那么Excel就会显示出一大长串「#」的乱码。

这里就要用数字的方法来进行处理,我们可以将自定义单元格格式设置为「0-00-00」来将这种数字转换成日期(仅仅看到是日期,并不支持计算)。

日期和时间的合并

相信使用Excel的同学,大部分都知道&这个符号,是将单元格内容连接到一起的符号。在操作中很多同学会将日期和时间使用&符号进行连接,其实这是错误的,在前面我们说过日期的本质是“数字”,时间的本质是“小数”,用&连接日期和时间,本质上就变成了数字连接小数了。

其实日期和时间并不用&符号进行连接,我们说到Excel中的标准日期是支持计算的,这里只需要将2个单元格相加即可。

那么关于日期数据类型的分享就暂时到这里了,当然在Excel中还有非常多的日期数据类型引用技巧,例如:日期连接日期、日期相加数字、日期减去时间等等,无论有多少变换的花样,只要记住一点:日期的本质是数字,从数字的角度上去思考日期,就可以得心应手

赞(1)
未经允许不得转载:芮和 » Excel日期数据的“########”

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

买域名,就上缘分域名!

立即前往联系卖家