如何修改Excel中的非法日期
我们在使用Excel的时候,有很多人对日期数据的输入是很随便的,有时输入的“日期”并不是真正的日期格式。而从数据库导入的数据中,很多情况下的日期也并不是真正的日期,而是文本。因此,不论什么时候拿到什么样的表格。首先要检查表格中的日期是否为真正的日期。今天我们就来学习下如何修改Excel中的非法日期。
在Excel中,日期是作为数值存储在单元格中的。因此,尽管显示格式多种多样,它们实际上都是数值。
Excel将日期存储为序列号(称为序列值)。默认情况下,1900年1月1日是序列号1.依此类推。2表示1900年1月2日。3表示1900年1月3日…一40 278就是2010年4月10日。在处理日期数据时。记住一点就可以了:日期数字永远是正整数。
因此,假如在单元格中输入了一个数字40 278.将其转换为日期。就表示2010年4月10日。
Excel所能处理的日期最小值是1900年1月0号(对应数字0)。日期最大值是9999年12月31号(对应数字2 958—465)。0-2 958 465之间的数字都可以转换为台法的日期和时间。如果输入的数字超出了这个区间。就不能转换为合法日期。而是非法的日期了。
例如在单元格中输入20100410,尽管它是一个数字。但超过了Excel所能处理的最大日期数字2 958 465。
了解了Excel处理日期的基本规则。在输入日期时就需要按照正确的格式输入。输入日期的正确格式是年、月、日3个数字之间用减号(-)或者斜杠(/)隔开。倒如。正确的日期输入格式为2010-4-10、2010t/4/10、10/Apt/2010或者10-Apt-2010。但是。如果输入2010.04.10或者20100410就大错特错了。
用户可以按照习惯采用一种简单的方法输入日期。倒如。要输入日期2010-4-10.下面的任何一种方法都是可行的:
键入“2010-4-10”;
·键入“2010/4/10”:
·键入“2010年4月10日”:
·键入“4-10”;
·键入“4/10”:
·键入“4月10日”:
·键入。10-4-10”:
·键入“10/4/10”;
·键入“10-Apr-10”:
·键入“10一Apr一2010”;
·键入“10一Apr”;
·键入“Apt一10”。
此外,Excel接受采用两位数宇输入的年份。但会进行不同的处理。
·00—29:Excel将00—29之间两位数字的年解释为2000-2029年。例如。输入日期
“19-5-28.Excel假定该日期为2019年5月28日。
·30—99:Excel将30—99之间两位数字的年解释为1930-1999年。例如。输入日期
“98-5-28”。Excel将假定该日期为1998年5月28日。
如果工作表中存在有大量的非法日期。就必须先将这些非法日期修改为真正的日期。根据实际情况。可以采用不同的方法。下面举例进行说明。
在很多情况下。某列的日期数据都是非法日期。或者大部分是非法日期。这主要发生在从数据库导入数据的场合。此时。一十一个地修改单元格是不现实的。可以使用。分列。工具批量修改非法日期。
图1所示为一些非法日期的例子。将这些非法日期转换为真正日期的量简便方法。是利用“分列”工具,下面介绍其具体方法和步骤。
图1
1、选择B列数据。注意每次只能选择一列数据。
2、单击“数据”选项卡。再单击“数据工具”功能组中的“分列”按钮,如图2所示。
图2
此时会打开“文本分列向导-步骤之1(共3步)”话框,如图3所示。
图3
3、单击两次“下一步”按钮,打开“文本分列向导-步骤之3共3步”对话框,在“列散据格式”选项组中选择“日期”单选按钮,在右侧的下拉列表框中选择日期格式“YMD”,如图4所示。
图4
4、单击“完成”按钮,即可将B列的非法日期转换为真正的日期。
其他列非法日期的转换也是采用这种方法如法炮制。最后的结果如图5所示。
图5
需要注意的是,在转换J列的非法日期时,在“文本分列向导-步骤之3(共3步)对话框中,要在日期格式下拉列表框中要选择“MDY”格式,因为原始数据的格式是“月-日-年”。
有些人在设计合同管理表格时。会把合同的起始日期和截止日期放在一起。如图6所示。这种表格结构尽管看起来比较紧凑。数据表示得也比较清楚。但是非常不利于数据分析。倒如。无法把某年某月签订合同或者续签合同要到期的员工筛选出来。也无法设计一个台同到期提前提醒功能。
图6
规范的表格应该是把合同的起始日期和截止日期分成两列保存。而不是图6所示的那样保存在一列中。如果已经做成了这样的表格。怎样将表格整理成规范的表格呢?
仔细观察表格数据。可以看出日期数据是比较标准和有规则的。都是诸如“2006-09-01”这样的数据。它有10个宇符。这样就可以使用LEFT函数和RIGHT函数把合同的起始日期和截止日期分别取出来。具体步骤如下:
1、重斯设计表格,在第一行下面插入一行,在E列右侧插入两列,在H列右侧插入两列,输入标题,并合并有关的单元榕,如图7所示。
图7
2、在单元格F3中输入公式“=DATEVALUE(LEFT(E3,10))”,并向下复制,得到第一次合同的开始日期。在单元格C3中输入公式“=DATEVALUE(RIGHT(E3,10))”,并向下复制,得到第一次台同的截止日期。
3、在单元格I3中输入公式“=IF(H3="","",DATEVALUE(LEFT(H3,10)))”,并向下复制,得到续签合同的开始日期。
在单元格J 3中输入公式“=IF(H3="","",DATEVALUE(RIGHT(H3,10)))”,并向下复制得到续签合同的截止日期。结果如图8所示。
图8
注意:利用文本函数LEFT和RIGHT得到的日期数据是文本日期,因此在公式中还必须使用DATEVALUE函数将这个文本日期转换为真正的日期。
4、采用选择性粘贴的方法,将单元格中的公式转换为数值,方法是:选择整个数据区域,按【Ctrl+C】组合键复制,再右击要粘贴的位置,在弹出的快捷菜单中选择“选择性粘贴”命令,打开“选择性粘贴”对话框。选择“数值”单选按钮,如图9所示。单击“确定”按钮。
图9
5、删除原始的两列合同日期。即可得到规范的合同管理表格,如图1 0所示。
图10
虽然我们学会了如何修改Excel中的非法日期,但是我们最好还是在建立表格的时候就创建正确的日期格式,这样我们就不用在后面进行修复日期了,这样对我们做数据分析也有很大的好处。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
在职场上,拍脑袋做决策的时代早已过去。数据分析正在成为每个职场人的核心竞争力,不仅能帮你找到问题,还能提供解决方案,提升 ...
2024-12-24Excel是数据分析的重要工具,强大的内置功能使其成为许多分析师的首选。在日常工作中,启用Excel的数据分析工具库能够显著提升数 ...
2024-12-23在当今信息爆炸的时代,数据分析师如同一位现代社会的侦探,肩负着从海量数据中提炼出有价值信息的重任。在这个过程中,掌握一系 ...
2024-12-23在现代的职场中,制作吸引人的PPT已经成为展示信息的重要手段,而其中数据对比的有效呈现尤为关键。为了让数据在幻灯片上不仅准 ...
2024-12-23在信息泛滥的现代社会,数据分析师已成为企业决策过程中不可或缺的角色。他们的任务是从海量数据中提取有价值的洞察,帮助组织制 ...
2024-12-23在数据驱动时代,数据分析已成为各行各业的必需技能。无论是提升个人能力还是推动职业发展,选择一条适合自己的学习路线至关重要 ...
2024-12-23在准备数据分析师面试时,掌握高频考题及其解答是应对面试的关键。为了帮助大家轻松上岸,以下是10个高频考题及其详细解析,外加 ...
2024-12-20互联网数据分析师是一个热门且综合性的职业,他们通过数据挖掘和分析,为企业的业务决策和运营优化提供强有力的支持。尤其在如今 ...
2024-12-20在现代商业环境中,数据分析师是不可或缺的角色。他们的工作不仅仅是对数据进行深入分析,更是协助企业从复杂的数据信息中提炼出 ...
2024-12-20随着大数据时代的到来,数据驱动的决策方式开始受到越来越多企业的青睐。近年来,数据分析在人力资源管理中正在扮演着至关重要的 ...
2024-12-20在数据分析的世界里,表面上的技术操作只是“入门票”,而真正的高手则需要打破一些“看不见的墙”。这些“隐形天花板”限制了数 ...
2024-12-19在数据分析领域,尽管行业前景广阔、岗位需求旺盛,但实际的工作难度却远超很多人的想象。很多新手初入数据分析岗位时,常常被各 ...
2024-12-19入门数据分析,许多人都会感到“难”,但这“难”究竟难在哪儿?对于新手而言,往往不是技术不行,而是思维方式、业务理解和实践 ...
2024-12-19在如今的行业动荡背景下,数据分析师的职业前景虽然面临一些挑战,但也充满了许多新的机会。随着技术的不断发展和多领域需求的提 ...
2024-12-19在信息爆炸的时代,数据分析师如同探险家,在浩瀚的数据海洋中寻觅有价值的宝藏。这不仅需要技术上的过硬实力,还需要一种艺术家 ...
2024-12-19在当今信息化社会,大数据已成为各行各业不可或缺的宝贵资源。大数据专业应运而生,旨在培养具备扎实理论基础和实践能力,能够应 ...
2024-12-19阿里P8、P9失业都找不到工作?是我们孤陋寡闻还是世界真的已经“癫”成这样了? 案例一:本硕都是 985,所学的专业也是当红专业 ...
2024-12-19CDA持证人Louis CDA持证人基本情况 我大学是在一个二线城市的一所普通二本院校读的,专业是旅游管理,非计算机非统计学。毕业之 ...
2024-12-18最近,知乎上有个很火的话题:“一个人为何会陷入社会底层”? 有人说,这个世界上只有一个分水岭,就是“羊水”;还有人说,一 ...
2024-12-18在这个数据驱动的时代,数据分析师的技能需求快速增长。掌握适当的编程语言不仅能增强分析能力,还能帮助分析师从海量数据中提取 ...
2024-12-17