文本转换成日期的格式(时间格式之间的转换技巧)

Excel的数据格式分为常规、数字、货币、短日期、长日期、时间、百分比、分数、文本等多种类型,各种类型之间基本上都是可以相互转换的,但必须掌握一定的技巧,例如文本与日期、时间格式之间的转换,必须要用到Datevalue等函数。


一、Excel函数:Datevalue。

功能:将文本格式的日期转换为日期序列号。

语法结构:=Datevalue(文本格式日期)。

注意实现:

1、Datevalue函数的参数必须以文本格式输入,日期必须要加双引号,否则返回错误值“#VALUE!” 。

2、如果省略参数中的年份,则默认为当前年份。

目的:计算两个日期之间相差的天数。

文本转换成日期的格式(时间格式之间的转换技巧)(1)

方法:

在目标单元格中输入公式:=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、参数必须以文本格式输入,时间必须加双引号。

目的:计算加班费用。

文本转换成日期的格式(时间格式之间的转换技巧)(2)

方法:

在目标单元格中输入公式:=ROUND(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(C3,"分",""),"时",":"))*24*80,0)&"元"。

解读:

由于C类的加班时长包含“分”和“时”文字,因此不能直接使用Timevalue函数将其转换为可用于计算的时间,所以用第一个Substitute函数将“分”替换为空,然后用第二个Substitute将“时”替换为“:”,最后使用Timevalue函数将文本格式的时间转换为可以进行计算的时间,再乘以24将其转换为小时,最后乘以每小时加班费用80并使用Round函数进行取整,得到加班费。


三、Excel函数:Datedif。

功能:以指定的方式统计两个日期之间相差的“年”、“月”或“天”等。

语法结构:=Datedif(开始日期,结束日期,统计方式)。

其中【统计方式】有6种,分别为:

文本转换成日期的格式(时间格式之间的转换技巧)(3)

注意事项:

1、【开始日期】和【结束日期】可以是表示日期的序列号,日期文本或单元格引用。

2、【开始日期】和【结束日期】表示的日期必须是标准的日期格式,或者用Date、Now、Today等函数输入的日期。

3、Datedif函数为系统隐藏函数,所以不能从【插入函数】对话框中插入该函数,而只能手动输入,仅适用于16及以上版本,高版本的WPS中也适用哦!

目的:统计办公用品的使用年限。

文本转换成日期的格式(时间格式之间的转换技巧)(4)

方法:

在目标单元格中输入公式:=DATEDIF(C3,TODAY(),"y")&"年"。

解读:

1、公式中的结束日期用Today()函数替代,即获取当前的日期,统计方式为“Y”,即统计两个日期之间相差的年份。

2、此函数还经常用于计算年龄,如果“购买时间”为“出生日期”,则只需将公式=DATEDIF(C3,TODAY(),"y")&"年"中的“年”替换为“岁”即可。


四、Excel函数:Days360。

功能:按照一年360天的算法,返回两个日期之间相差的天数。

语法结构:=Days360(开始日期,结束日期,[统计方式])。

其中【统计方式】分为两种,一种为欧洲方法,另一种为美国方法;当为False或省略时,为美国方法,即如果开始日期是一个月的最后一天,则等同于同月的30号;如果结束日期是一个月的最后一天,并且开始日期早于30号,结束日期等同于下一个月的1号。当为True时,开始和结束日期为一个月的31号,都将等同于30号。

注意事项:

1、【开始日期】和【结束日期】可以是表示日期的序列号,日期文本或单元格引用。

2、【开始日期】和【结束日期】表示的日期必须是标准的日期格式,或者用Date、Now、Today等函数输入的日期。

目的:计算还款天数。

文本转换成日期的格式(时间格式之间的转换技巧)(5)

方法:

在目标单元格中输入公式:=DAYS360(C3,D3,0)&"天"。

解读:

0为False,即以美国方法进行统计。


五、Excel函数:Days。

功能:计算两个日期之间相差的天数。

语法结构:=Days(结束日期,开始日期)。

注意事项:

1、【开始日期】和【结束日期】可以是表示日期的序列号,日期文本或单元格引用。

2、【开始日期】和【结束日期】表示的日期必须是标准的日期格式,或者用Date、Now、Today等函数输入的日期。

目的:计算还款天数。

文本转换成日期的格式(时间格式之间的转换技巧)(6)

方法:

在目标单元格中输入公式:=DAYS(D3,C3)&"天"。

解读:

从计算结果中可以看出,Days的计算结果和Datedif以“d”为统计方式时的值相同。


六、Excel函数:Edate。

功能:计算指定日期之间或之后相隔月份的日期。

语法结构:=Edate(开始日期,月份)。

如果【月份】的值为正数,则表示未来的X个月,如果为负数,则表示过去的X个月,如果为小数,只保留整数部分。

注意事项:

1、【开始日期】和【结束日期】可以是表示日期的序列号,日期文本或单元格引用。

2、【开始日期】和【结束日期】表示的日期必须是标准的日期格式,或者用Date、Now、Today等函数输入的日期。

目的:计算还款日期。

文本转换成日期的格式(时间格式之间的转换技巧)(7)

方法:

在目标单元格中输入公式:=TEXT(EDATE(C3,LEFT(LEN(D3)-2)),"yyyy年m月d日")。

解读:

公式LEFT(LEN(D3)-2)用于从指定的单元格中提取数值,作为Edate函数的第二个参数,最后使用Text函数将计算出的还款日期设置为日期格式。


七、Excel函数:Datestring。

功能:将指定的日期序列号转换为文本日期。

语法结构:=Datestring(值或引用)。

注意事项:

1、【值或引用】可以是表示日期的序列号,日期文本或单元格引用。

2、【值或引用】表示的日期必须是标准的日期格式,或者用Date、Now、Today等函数输入的日期。

目的:计算还款日期。

文本转换成日期的格式(时间格式之间的转换技巧)(8)

方法:

在目标单元格中输入公式:=DATESTRING(EDATE(C3,LEFT(D3,LEN(D3)-2)))。

解读:

首先用Edate函数获取对应日期的序列号,然后用Datestring函数转换为对应的文本日期。


结束语:

文本从实际应用出发,对工作中经常要用到的文本、日期和时间之间的转换技巧做了解读,希望对各位亲的工作有所帮助哦!


#我要上# #Excel函数公式# #职场达人炼成记#

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页