2017年最全的excel函数大全6—日期和时间函数(上)

上次给大家分享了 《2017年最全的excel函数大全(5)——逻辑函数》 ,这次分享给大家日期和时间函数(上)。

DATE 函数

返回特定日期的序列号

描述

DATE 函数返回表示特定日期的连续序列号。

用法

DATE(year,month,day)

DATE 函数用法具有下列参数:

ü  Year:必需。year 参数的值可以包含一到四位数字。Excel 将根据计算机正在使用的日期系统来解释 year 参数。默认情况下,Microsoft Excel for Windows 使用的是 1900 日期系统,这表示第一个日期为 1900 年 1 月 1 日。

提示: 为避免出现意外结果,请对 year 参数使用四位数字。例如,“07”可能意味着“1907”或“2007”。因此,使用四位数的年份可避免混淆。

·        如果 year 介于 0(零)到 1899 之间(包含这两个值),则 Excel 会将该值与 1900 相加来计算年份。例如,DATE(108,1,2) 返回 2008 年 1 月 2 日 (1900+108)。

·        如果 year 介于 1900 到 9999 之间(包含这两个值),则 Excel 将使用该数值作为年份。例如,DATE(2008,1,2) 将返回 2008 年 1 月 2 日。

·        如果 year 小于 0 或大于等于 10000,则 Excel 返回 错误值 #NUM!。

ü  月:必需。 一个正整数或负整数,表示一年中从 1 月至 12 月(一月到十二月)的各个月。

·        如果 month 大于 12,则 month 会从指定年份的第一个月开始加上该月份数。例如,DATE(2008,14,2) 返回表示 2009 年 2 月 2 日的序列数。


·        如果 month 小于 1,则 month 会从指定年份的第一个月开始减去该月份数,然后再加上 1 个月。例如,DATE(2008,-3,2) 返回表示 2007 年 9 月 2 日的序列号。

ü  日:必需。 一个正整数或负整数,表示一月中从 1 日到 31 日的各天。

·        如果 day 大于指定月中的天数,则 day 会从该月的第一天开始加上该天数。例如,DATE(2008,1,35) 返回表示 2008 年 2 月 4 日的序列数。

·        如果 day 小于 1,则 day 从指定月份的第一天开始减去该天数,然后再加上 1 天。例如,DATE(2008,1,-15) 返回表示 2007 年 12 月 16 日的序列号。

注意: Excel 可将日期存储为连续序列号,以便能在计算中使用它们。1900 年 1 月 1 日的序列号为 1,2008 年 1 月 1 日的序列号为 39448,这是因为它与 1900 年 1 月 1 日之间相差 39,447 天。需要更改数字格式(设置单元格格式)以显示正确的日期。

案例

案例 1



例如:=DATE(C2,A2,B2) 将单元格 C2 中的年、单元格 A2 中的月以及单元格 B2 中的日合并在一起,并将它们放入一个单元格内作为日期。以下案例显示了单元格 D2 中的最终结果。

案例 2根据其他日期计算某个日期

可以使用 DATE 函数创建基于其他单元格中日期的一个日期。例如,可以使用 YEAR、MONTH 和 DAY 函数来创建基于另一个单元格的周年纪念日期。假设,某个员工第一天上班的日期为 2016 年 10 月 1 日,则可以使用 DATE 函数创建他上班 5 周年的纪念日期:

 

1.     DATE 函数会创建一个日期。

2.     =DATE(YEAR(C2)+5,MONTH(C2),DAY(C2))

3.     YEAR 函数会查找单元格 C2 并从中提取“2012”。

4.     “+5”表示加上 5 年,并在单元格 D2 中创建“2017”作为周年纪念日的年。

5.     MONTH 函数从单元格 C2 中提取“3”。这将在单元格 D2 中创建“3”作为月。

6.     DAY 函数从单元格 C2 中提取“14”。这将在单元格 D2 中创建“14”作为天。

案例 3 将文本字符串和数字转换为日期

有时Excel的日期是无法识别的。这可能是因为数字与典型的日期不相似,也可能因为数据被设置成了文本格式。如果是这种情况,则可以使用 DATE 函数将信息转换成日期。例如,在下图中,单元格 C2 包含采用以下格式的日期:YYYYMMDD。它也被设置成了文本格式。若要将其转换成日期,则可以将 DATE 函数与 LEFT、MID 和 RIGHT 函数配合使用。

 

1.     DATE 函数会创建一个日期。

2.     =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

3.     LEFT 会在单元格 C2 中查找并从左起提取前 4 个字符。这将在单元格 D2 中创建“2014”作为转换后日期的年。

4.     MID 函数将在单元格 C2 中查找。它将从第 5 个字符开始,然后向右提取 2 个字符。这将在单元格 D2 中创建“03”作为转换后日期的月。因为 D2 的格式设置为 Date,因此“0”不包括在最终结果中。

5.     RIGHT 函数会在单元格 C2 中查找,然后从最右侧开始向左提取前 2 个字符。这将在 D2 中创建“14”作为日期的日。

案例 4 按一定的天数加减日期 

若要按一定的天数加减日期,只需向值或包含日期的单元格引用加上或减去天数即可。

在以下案例中,单元格 A5 包含我们想加上和减去 7 天(C5 中的值)的日期。


DATEDIF 函数


计算两个日期之间的天数、月数或年数。

描述

计算两个日期之间相隔的天数、月数或年数。警告:Excel 提供了 DATEDIF 函数,以便支持来自 Lotus 1-2-3 的旧版工作簿。在某些应用场景下,DATEDIF 函数计算结果可能并不正确。有关详细信息,请参阅本文中的“已知问题”部分。

用法

DATEDIF(start_date,end_date,unit)

ü  Start_date:用于表示时间段的第一个(即起始)日期的日期。 日期值有多种输入方式:带引号的文本字符串(例如 "2001/1/30")、序列号(例如 36921,在商用 1900 日期系统时表示 2001 年 1 月 30 日)或其他公式或函数的结果(例如 DATEVALUE("2001/1/30"))。

ü  End_date:用于表示时间段的最后一个(即结束)日期的日期。

ü  Unit:要返回的信息类型:

 

其他

l  日期存储为可用于计算的序列号。默认情况下,1899 年 12 月 31 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。

l  DATEDIF 函数在用于计算年龄的公式中很有用。


案例


已知问题

“MD”参数可能导致出现负数、零或不准确的结果。若要计算上一完整月份后余下的天数,可使用如下方法:

此公式从单元格 E17 中的原始结束日期 (5/6/2016) 减去当月第一天 (5/1/2016)。其原理如下:首先,DATE 函数会创建日期 5/1/2016。DATE 函数使用单元格 E17 中的年份和单元格 E17 中的月份创建日期。1 表示该月的第一天。DATE 函数的结果是 5/1/2016。然后,从单元格 E17 中的原始结束日期(即 5/6/2016)减去该日期。5/6/2016 减 5/1/2016 得 5 天。

DATEVALUE 函数

将文本格式的日期转换为序列号

描述

DATEVALUE 函数将存储为文本的日期转换为 Excel 识别为日期的序列号。 例如,公式=DATEVALUE("1/1/2008") 返回 39448,即日期 2008-1-1 的序列号。 即使如此,请注意,计算机的系统日期设置可能会导致 DATEVALUE 函数的结果会与此案例不同。

如果工作表包含采用文本格式的日期并且要对这些日期进行筛选、排序、设置日期格式或执行日期计算,则 DATEVALUE 函数将十分有用。

用法

DATEVALUE(date_text)

DATEVALUE 函数用法具有下列参数:

ü  Date_text    必需。代表采用 Excel 日期格式的日期的文本,或是对包含这种文本的单元格的引用。例如,用于表示日期的引号内的文本字符串 "2008-1-30" 或 "30-Jan-2008"。

·        使用 Microsoft Excel for Windows 中的默认日期系统时,参数 date_text 必须代表 1900 年 1 月 1 日和 9999 年 12 月 31 日之间的某个日期。 如果参数 date_text的值在此范围之外, DATEVALUE函数将返回错误值 “#VALUE!。

·        如果省略参数 date_text 中的年份部分,则 DATEVALUE 函数会使用计算机内置时钟的当前年份。 参数 date_text 中的时间信息将被忽略。

其他

l  Excel 可将日期存储为序列号,以便可以在计算中使用它们。 默认情况下,1900 年 1 月 1 日的序列号为 1,2008 年 1 月 1 日的序列号为 39,448,这是因为它距 1900 年 1 月 1 日有 39,447 天。

l  大部分函数都会自动将日期值转换为序列数。

案例


DAY 函数

将序列号转换为月份日期

描述

返回以序列数表示的某日期的天数。 天数是介于 1 到 31 之间的整数。

用法

DAY(serial_number)

DAY 函数用法具有下列参数:

ü  Serial_number    必需。要查找的日期。应使用 DATE 函数输入日期,或将日期作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。

其他

l  Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下,1900 年 1 月 1 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。

l  无论提供的日期值的显示格式如何,YEAR、MONTH 和 DAY 函数返回的值都是公历值。例如,如果提供的日期的显示格式是回历,则 YEAR、MONTH 和 DAY 函数返回的值将是与对应的公历日期相关联的值。

案例


DAYS 函数

返回两个日期之间的天数

描述

返回两个日期之间的天数。

用法

DAYS(end_date, start_date)

DAYS 函数用法具有以下参数。

ü  End_date    必需。 Start_date 和 End_date 是用于计算期间天数的起止日期。

ü  Start_date    必需。Start_date 和 End_date 是用于计算期间天数的起止日期。

注意: Excel 可将日期存储为序列号,以便可以在计算中使用它们。 默认情况下,1900 年 1 月 1 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39447 天。

其他

l  如果两个日期参数为数字,DAYS 使用 EndDate–StartDate 计算两个日期之间的天数。

l  如果任何一个日期参数为文本,该参数将被视为 DATEVALUE(date_text) 并返回整型日期,而不是时间组件。

l  如果日期参数是超出有效日期范围的数值,DAYS 返回 #NUM! 错误值。

l  如果日期参数是无法解析为字符串的有效日期,DAYS 返回 #VALUE! 错误值。

案例

 

DAYS360 函数

以一年 360 天为基准计算两个日期间的天数

描述

按照一年 360 天的算法(每个月以 30 天计,一年共计 12 个月),DAYS360 函数返回两个日期间相差的天数,这在一些会计计算中将会用到。 如果财会系统是基于一年 12 个月,每月 30 天,可使用此函数帮助计算支付款项。

用法

DAYS360(start_date,end_date,[method])

DAYS360 函数用法具有下列参数:

ü  Start_date、end_date    必需。 用于计算期间天数的起止日期。 如果 start_date 在 end_date 之后,则 DAYS360 函数将返回一个负数。 应使用 DATE 函数输入日期,或者将从其他公式或函数派生日期。 例如,使用函数 DATE(2008,5,23) 以返回 2008 年 5 月 23 日。 如果日期以文本形式输入,则会出现问题。

ü  方法    可选。 逻辑值,用于指定在计算中是采用美国方法 还是欧洲方法。


注意: Excel 可将日期存储为序列号,以便可以在计算中使用它们。 默认情况下,1900 年 1 月 1 日的序列号为 1,2008 年 1 月 1 日的序列号为 39,448,这是因为它距 1900 年 1 月 1 日有 39,447 天。

案例

 

EDATE 函数

返回用于表示开始日期之前或之后月数的日期的序列号

描述

返回表示某个日期的序列号,该日期与指定日期 (start_date) 相隔(之前或之后)指示的月份数。 使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期。

用法

EDATE(start_date, months)

EDATE 函数用法具有以下参数:

ü  Start_date    必需。一个代表开始日期的日期。应使用 DATE 函数输入日期,或将日期作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。

ü  Months    必需。 start_date 之前或之后的月份数。 months 为正值将生成未来日期;为负值将生成过去日期。

其他

  • Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下,1900 年 1 月 1 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。

  • 如果 start_date 不是有效日期,则 EDATE 返回 错误值 #VALUE!。

  •      如果 months 不是整数,将截尾取整。

  • 案例



    EOMONTH 函数

    返回指定月数之前或之后的月份的最后一天的序列号

    描述

    返回某个月份最后一天的序列号,该月份与 start_date 相隔(之后或之后)指示的月份数。 使用函数 EOMONTH 可以计算正好在特定月份中最后一天到期的到期日。

    用法

    EOMONTH(start_date, months)

    EOMONTH 函数用法具有以下参数:

    ü  Start_date    必需。一个代表开始日期的日期。应使用 DATE 函数输入日期,或将日期作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。

    ü  Months    必需。 start_date 之前或之后的月份数。 months 为正值将生成未来日期;为负值将生成过去日期。

    注意: 如果 months 不是整数,将截尾取整。

    其他

    l  Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下,1900 年 1 月 1 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。

    l  如果 start_date 不是有效日期,则 EOMONTH 返回 错误值 #NUM!。

    l  如果 start_date 加 months 产生非法日期值,则 EOMONTH 返回 错误值 #NUM!。

    案例

     

    HOUR 函数

    将序列号转换为小时

    描述

    返回时间值的小时数。 小时数是介于 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之间的整数。

    用法

    HOUR(serial_number)

    HOUR 函数用法具有下列参数:

    ü  Serial_number    必需。 时间值,其中包含要查找的小时数。 时间值有多种输入方式:带引号的文本字符串(例如 "6:45 PM")、十进制数(例如 0.78125 表示 6:45 PM)或其他公式或函数的结果(例如 TIMEVALUE("6:45 PM"))。

    其他

  • 时间值为日期值的一部分,并用十进制数表示(例如 12:00 PM 可表示为 0.5,因为此时是一天的一半)。

  • 案例


    ISOWEEKNUM 函数

    返回给定日期在全年中的 ISO 周数

    描述

    返回给定日期在全年中的 ISO 周数。

    用法

    ISOWEEKNUM(date)

    ISOWEEKNUM 函数用法具有下列参数:

    ü  日期    必需。 日期是 Excel 用于日期和时间计算的日期-时间代码。

    其他

    l  Microsoft Excel 可将日期存储为可用于计算的序列数字。 默认情况下,1900 年 1 月 1 日的序列数字是 1,而 2008 年 1 月 1 日的序列数字是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。

    l  如果日期参数为无效数值,则 ISOWEEKNUM 将返回错误值 #NUM! 。

    l  如果日期参数为无效日期类型,则 ISOWEEKNUM 将返回错误值 #VALUE! 。

    案例

    MINUTE 函数

    将序列号转换为分钟

    描述

    返回时间值中的分钟。 分钟是一个介于 0 到 59 之间的整数。

    用法

    MINUTE(serial_number)

    MINUTE 函数用法具有下列参数:

    ü  Serial_number    必需。 一个时间值,其中包含要查找的分钟。 时间值有多种输入方式:带引号的文本字符串(例如 "6:45 PM")、十进制数(例如 0.78125 表示 6:45 PM)或其他公式或函数的结果(例如 TIMEVALUE("6:45 PM"))。

    其他

  • 时间值为日期值的一部分,并用十进制数表示(例如 12:00 PM 可表示为 0.5,因为此时是一天的一半)。

  • 案例


    MONTH 函数

    将序列号转换为月

    描述

    返回日期(以序列数表示)中的月份。 月份是介于 1(一月)到 12(十二月)之间的整数。

    用法

    MONTH(serial_number)

    MONTH 函数用法具有下列参数:

  • Serial_number    必需。要查找的月份日期。应使用 DATE 函数输入日期,或将日期作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。

  • 其他

  • Microsoft Excel 可将日期存储为可用于计算的序列号。默认情况下,1900 年 1 月 1 日的序列号是 1,而 2008 年 1 月 1 日的序列号是 39448,这是因为它距 1900 年 1 月 1 日有 39448 天。

  • 无论提供的日期值的显示格式如何,YEAR、MONTH 和 DAY 函数返回的值都是公历值。例如,如果提供的日期的显示格式是回历,则 YEAR、MONTH 和 DAY 函数返回的值将是与对应的公历日期相关联的值。

  • 案例


    NETWORKDAYS 函数

    返回两个日期间的完整工作日的天数

    描述

    返回参数 start_date 和 end_date 之间完整的工作日数值。 工作日不包括周末和专门指定的假期。 可以使用函数 NETWORKDAYS,根据某一特定时期内雇员的工作天数,计算其应计的报酬。

    提示: 若要使用参数来指明周末的日期和天数,从而计算两个日期间的全部工作日数,请使用 NETWORKDAYS.INTL 函数。

    用法

    NETWORKDAYS(start_date, end_date, [holidays])

    NETWORKDAYS 函数用法具有下列参数:

    ü  Start_date    必需。 一个代表开始日期的日期。

    ü  End_date    必需。 一个代表终止日期的日期。

    ü  Holidays    可选。不在工作日历中的一个或多个日期所构成的可选区域,例如:省/市/自治区和国家/地区的法定假日以及其他非法定假日。该列表可以是包含日期的单元格区域,或是表示日期的序列号的数组常量。

    重要: 应使用 DATE 函数输入日期,或者将日期作为其他公式或函数的结果输入。 例如,使用函数 DATE(2012,5,23) 输入 2012 年 5 月 23 日。 如果日期以文本形式输入,则会出现问题。

    其他

  • Microsoft Excel 可将日期存储为可用于计算的序列号。 默认情况下,1900 年 1 月 1 日的序列号为 1,2012 年 1 月 1 日的序列号为 40909,这是因为它距 1900 年 1 月 1 日有 40,909 天。

  • 如果任一参数不是有效日期,则 NETWORKDAYS 返回 错误值 #VALUE!。

  • 案例

     

    NETWORKDAYS.INTL 函数

    返回两个日期之间的完整工作日的天数

    描述

    返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。 周末和任何指定为假期的日期不被视为工作日。

    用法

    NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

    NETWORKDAYS.INTL 函数用法具有以下参数:

    ü  start_date 和 end_date    必需。 要计算其差值的日期。 start_date 可以早于或晚于 end_date,也可以与它相同。

    ü  weekend    可选。 表示介于 start_date 和 end_date 之间但又不包括在所有工作日数中的周末日。 Weekend 是一个用于指定周末日的周末数字或字符串。

    weekend 数值表示以下周末日:

     

    周末字符串值的长度为七个字符,并且字符串中的每个字符表示一周中的一天(从星期一开始)。 1 表示非工作日,0 表示工作日。 在字符串中仅允许使用字符 1 和 0。 使用 1111111 将始终返回 0。

    例如,0000011 结果为星期六和星期日是周末。

    ü  holidays    可选。 一组可选的日期,表示要从工作日日历中排除的一个或多个日期。 holidays 应是一个包含相关日期的单元格区域,或者是一个由表示这些日期的序列值构成的数组常量。 holidays 中的日期或序列值的顺序可以是任意的。

    其他

    l  如果 start_date 晚于 end_date,则返回值将为负数,数量将是所有工作日的数量。

    l  如果 start_date 在当前日期基准值的范围之外,则 NETWORKDAYS.INTL 返回 错误值 #NUM!。

    l  如果 end_date 在当前日期基准值的范围之外,则 NETWORKDAYS.INTL 返回 错误值 #NUM!。

    l  如果 weekend 字符串的长度无效或包含无效字符,则 NETWORKDAYS.INTL 返回 错误值 #VALUE!。

    案例

    以上是所有EXCEL的日期和时间函数(上)说明语法以及使用案例。这次分享中存在哪些疑问或者哪些不足,可以在下面进行评论。如果觉得不错,可以分享给你的朋友,让大家一起掌握这些excel的逻辑函数。

    推荐阅读更多精彩内容