如何修改Excel中的非法日期
发布时间:2022-03-03 14:57:09
我们在使用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中的非法日期,但是我们最好还是在建立表格的时候就创建正确的日期格式,这样我们就不用在后面进行修复日期了,这样对我们做数据分析也有很大的好处。
猜你喜欢
- 怎么将图片中的表格怎么转换搭配excel表格中?朋友发来了很重要的数据,但是打开一看傻眼了,竟然是截图,该怎么获取图片中的表格数据呢?我们可
- 在Word 2016中编辑好文件后按保存键保存时,默认存储格式为Docx。但是很多场合,比如机关事业单位的申报填表、文件材料等等,都是以Do
- 在编辑word表格时,有时或许需要进行一些简单的计算。你可以使用“表格”菜单中的“公式”命令(Word2003)或者“表格工具”的“布局”选
- CSGO是现在很多玩家都在玩的射击竞技游戏,相信很多用户在玩这个游戏的时候总是出现一些卡顿、掉帧、闪退的情况。对于这些情况小编认为有可能是系
- 有时候为了美观所以要做一些好看的单元格,如果你的表格需要菱形、三角形之类的特殊单元格,那么Excel怎么让单元格形状更具特点?下面小编就为大
- Word具有统计字数的功能,用户可以方便地获取当前Word文档的字数统计信息,操作步骤如下所述:第1步,打开Word文档窗口,切换到“审阅”
- Excel2019怎么插入文本?这篇文章主要介绍了Excel2019插入文本教程,需要的朋友可以参考下excel2019如何插入文本,那么为
- 在win10系统之中,微软内置了蓝牙功能,这样让用户的文件传输有更好的体验。但是很多用户在需要用到蓝牙的时候却发现蓝牙的开关不见了,那么这种
- 在word艺术字的插入方法不是很了解。一般一些初学者刚接触到word办公软件就会可能遇到这种情况。那么在word艺术字在哪?该如何去插入艺术
- 用Excel2013打开需要生成柱状图的工作表,切换到“插入”选项卡,然后单击“插入柱形图”组中的“更多柱形图”选项,为了选择更多可用的效果
- 文档中经常会提及一些邮箱地址,如图 7‑65所示,笔者希望把一篇文章中所有的邮箱地址提取出来,如何批量处理呢? 图
- 在Word2010文档中,利用自选图形库提供的丰富的流程图形状和连接符可以制作各种用途的流程图,制作步骤如下所述:第1步:打开Word201
- Word如何设置悬挂缩进?悬挂缩进可以帮助我们把段落的第一行进行缩进,看起来更加美观,
- 这篇教程是向脚本之家的朋友分享excel中选择性粘贴的一些应用实例详解,教程真的很不错,对于有需要的朋友可以参本文,来看看吧有时需要把exc
- window10是当前最热门的一款操作系统,在使用过程中总会遇到各种故障问题,例如win10系统重启时会一直转圈圈,等待很久时间还是一样,也
- 写毕业论文的时候,需要在每一章中都要设置不同的页眉,如:在奇数页,页眉写上大学名称,偶数页写上章节题目,对于大多数人来说还是不太懂,下面小编
- 这里的应用场景如下:“ 在工作表 Sheet1 中存储着数据,现在想要在该工作表的第O 列至第T 列中搜索指定的数据,如果发现,则将数据所在
- 1.打开Excel表格,接着点击工具栏的“文件”→Excel选项 2.在Excel选项里点击“高级”菜单,在高级里
- word作为常用的办公软件,我们常常用它来处理一些资料,而有的时候,我们需要对打印出来的文档进行一个装订,而此时常常位于装订区的上边或左边,
- 在word中输入文字时候,会出现输入一个字后面的字就被覆盖的情况,有可能是你不小心碰了键盘上insert键将word文档设置成了改写的模式,