Excel的数据格式分为常规、数字、货币、短日期、长日期、时间、百分比、分数、文本等多种类型,各种类型之间基本上都是可以相互转换的,但必须掌握一定的技巧,例如文本与日期、时间格式之间的转换,必须要用到Datevalue等函数。
一、Excel函数:Datevalue。
功能:将文本格式的日期转换为日期序列号。
语法结构:=Datevalue(文本格式日期)。
注意实现:
1、Datevalue函数的参数必须以文本格式输入,日期必须要加双引号,否则返回错误值“#VALUE!” 。
2、如果省略参数中的年份,则默认为当前年份。
目的:计算两个日期之间相差的天数。
方法:
在目标单元格中输入公式:=DATEVALUE(B4&C4&D4)-DATEVALUE(B3&C3&D3)。
解读:
1、公式中首先利用=DATEVALUE(B4&C4&D4)和=DATEVALUE(B3&C3&D3)获取“2020年1月14日”和“2019年12月20日”的序列号43844和43819,然后求差值。
2、上述功能还可以用公式:=DATEDIF(B3&C3&D3,B4&C4&D4,"d")完成。Datedif函数的功能请参阅文章后续部分。
二、Excel函数:Timevalue。
功能:将文本格式的时间转换为时间序列号。
语法结构:=Timevalue(文本格式的时间)。
注意事项:
1、参数必须以文本格式输入,时间必须加双引号。
目的:计算加班费用。
方法:
在目标单元格中输入公式:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(C3,"分",""),"时",":"))*24*80,0)&"元"。
解读:
由于C类的加班时长包含“分”和“时”文字,因此不能直接使用Timevalue函数将其转换为可用于计算的时间,所以用第一个Substitute函数将“分”替换为空,然后用第二个Substitute将“时”替换为“:”,最后使用Timevalue函数将文本格式的时间转换为可以进行计算的时间,再乘以24将其转换为小时,最后乘以每小时加班费用80并使用Round函数进行取整,得到加班费。
三、Excel函数:Datedif。
功能:以指定的方式统计两个日期之间相差的“年”、“月”或“天”等。
语法结构:=Datedif(开始日期,结束日期,统计方式)。
其中【统计方式】有6种,分别为:
注意事项:
1、【开始日期】和【结束日期】可以是表示日期的序列号,日期文本或单元格引用。
2、【开始日期】和【结束日期】表示的日期必须是标准的日期格式,或者用Date、Now、Today等函数输入的日期。
3、Datedif函数为系统隐藏函数,所以不能从【插入函数】对话框中插入该函数,而只能手动输入,仅适用于16及以上版本,高版本的WPS中也适用哦!
目的:统计办公用品的使用年限。