中企动力 > 商学院 > excel求时间分钟差
  • ?

    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 ...

  • ?

    EXCEL-15个日期与时间函数的用法解析

    柯乐瑶

    展开

    1,DATE函数—将数组转换为日期。

    2,DATEDIF函数—计算两个日期间隔的时间。

    3,DAY函数—返回指定日期对应的天数。

    4,DAYS360函数—返回两日期间相差的天数。

    5,EDATE函数—返回表示某个日期相隔月份的序列号。

    6,EOMONTH函数—返回某个月份最后一天的序列号。

    7,HOUR函数—返回时间值的小时数。

    8,MINUTE函数—返回时间值中的分钟。

    9,MONTH函数—返回以序列号表示的日期中的月份。

    10,NETWORKDAYS函数—返回两日期 之间的工作日数值。

    11,WEEKNUM—返回特定日期的周数。

    12,WEEKDAY—返回某日期为星期几。

    13,WORKDAY—返回在某日期相隔指定工作日的日期值。

    14,SECOND—返回时间值的秒数。

    15,YEAR函数—返回某日期对应的年份。

  • ?

    excel你永远不知道的一个时间函数?

    解释

    展开

    excel除了内置的today、month、week等时间函数,还有一个你没见过的时间函数:datedif。datedif函数没有内置在excel当中,你在输入函数时不会有提示,但是实际上这个函数是存在的。尤其在计算工作时间,或者是两个时间段的时间差时,使用的非常普遍。

    图一:效果图

    下面请看datedif函数的使用方法:

    图二:动态图

    公式=datedif(开始时间,结束时间,“日期格式”)

    日期格式:Y:代表年、M:代表月、D:代表日

    在职年份:=DATEDIF(E3,F3,"Y")

    在职月份:=DATEDIF(E3,F3,"M")

    在职天数:=DATEDIF(E3,F3,"D")

    学习更多excel技能,请搜索并关注百家号:excel 。我们只提供最实用的excel函数与VBA使用技巧。

  • ?

    用EXCEL如何将日期和时间拆分

    野狐禅

    展开

    日期和时间在一个单元格,想把它折分成日期和时间存储在两个单元格中,该如何操作?今天带给大家拆分日期和时间的方法。数据表如下:

    01

    分列法

    02

    快速填充法

    03

    函数法

    公式原理:将A列设置为“常规”格式后,显示如下:

    用INT函数求整,得出的是日期序列号,再用A列数据减去B列对应的数据,得到的是小数部分。将B列设置为日期格式,C列设置为时间格式即可。

    如果单独得出时间,可以在C2单元格编辑如下公式并填充:

    =A2-INT(A2)

    =MOD(A2,1)

    往期文章

  • ?

    三个小时,整理出Excel中的时间计算套路

    凡人恋

    展开

    小伙伴们好啊,今天是节后上班第一天,怎么样,做好撸起袖子加油干的准备了吗?

    好时光总是过得太快,今天老祝就和大家说说Excel中,关于时间计算的套路。

    开始之前,咱们先说说时间数据的输入。

    要输入Excel能识别的时间,小时、分钟和秒必须以英文的冒号间隔,在实际输入时,如果以中文逗号间隔,Excel也会自动纠正:

    如果你输入的是8点20或是8.20,Excel是不能识别为时间格式的,这里一定要注意哦。

    如果是二B同事输入的时间数据,咱们也可以:

    快速转换时间格式

    复制内容粘贴到记事本中,按Ctrl+H键调出替换对话框,将小数点. 全部替换为英文冒号 :,再将数据复制回Excel。

    计算时间间隔小时

    要计算两个时间之间的间隔小时,可以使用公式:

    =(结束时间-开始时间)*24

    计算时间间隔分钟

    要计算两个时间之间的间隔分钟,可以使用公式:

    =(结束时间-开始时间)*1440

    计算时间间隔秒

    要计算两个时间之间的间隔秒数,可以使用公式:

    =(结束时间-开始时间)*24*60*60

    5秒男的同学请举手,咳咳……

    计算跨天的间隔小时

    跨天计算时间间隔时,直接相减会得出负数。如果不超过24小时,可以使用以下公式:

    =MOD(结束时间-开始时间,1)

    取整的间隔小时数

    计算两个时间的间隔小时数,不足一小时部分舍去,计算加班时经常会用到,说多了都是泪……

    =TEXT(B2-B1,"[h]")

    取整的间隔分钟数

    不足一分钟按一分钟计算,这个老祝就不说了,移动联通电信计算的都很好。

    =CEILING(B2-B1,1/1440)

    时间和日期的合并

    万般皆是数,要合并日期和时间,只需要做个加法:

    日期和时间的拆分

    要从日期时间数据中提取出日期,可以使用以下公式:

    =INT(A2)

    要继续提取时间,只需要做个减法,就欧了:

    好了,今天的分享就是这些吧,祝小伙伴们一周都顺利,天天好心情!

  • ?

    EXCEL表格中时间求和问题

    代秋

    展开

    小编今天给大家介绍一下如何计算时长,时长和的问题主要聚焦在1)分钟和时间转换问题2)如何提取时间数据3)处理异常数据如空值等等。来看下面示例:

    解决步骤:

    1.提取小时数据:

    E3=VALUE(IFERROR(LEFT(D3,FIND("小",D3,1)-1),0))

    解释为:FIND函数找“小”字在D3字符串的位置,减去1,作为左侧小时的位数,LEFT函数截取左侧的小时数,如果不存在小时或者没有记录则用IFERROR函数用0表示,最后用VALUE函数把截取到的数字字符串转换为数字格式。

    2.提取分钟数据:

    F3=VALUE(IFERROR(MID(D3,IFERROR(FIND("时",D3,1),0)+1,FIND("分",D3,1)-IFERROR(FIND("时",D3,1),0)-1),0))

    解释为:分别用FIND函数找“分”和“时”在D3中的位置。找到后用MID函数截取分钟数,起始位置就是FIND函数找“时”加上1,如果FIND“时”函数错误,用IFERROR把错误值变成0,同理截取的字符串个数就是FIND“分”的位置-FIND“时”的位置再减去1,然后套个IFERROR来排除空值错误显示为0,最后套个VALUE函数转变为数字。

    3.统一单位为分钟:

    G3=E3*60+F3

    解释为小时*60+分钟

    4.求和时长

    H3=CONCATENATE(INT(SUM(G3:G18)/60),"小时",MOD(SUM(G3:G18),60),"分钟")

    解释为:先把统一后的分钟数求和,然后除以60在向下取整作为小时数,再把除以60的余数作为分钟数,用到MOD函数求余数。最后用CONCATENATE函数连接,显示格式为:数字+小时+数字+分钟

    当然最后还可以不用统一为分钟,去掉G列,后面总小时数为E列的求和加上F列求和除以60的向下取整,然后F列的余数作为分钟数。

  • ?

    excel表格,如何计算日期的天数或时间差

    心非

    展开

    在excel中如何单独计算日期的天数或时间差。对于日期和时间,之间的差,例如:日期时间差的公式,结果要小时(如134.5小时),比如2016年2月10日 13:26到2016年3月6日 10:40相距多少个小时,怎么实现呢?

    首先,单独计算两个日期之间的天数。

    这个很简单,两个日期直接相减就可以得到两个日期间隔的天数,如下图:

    也可以用隐藏函数DATEDIF函数来完成,输入:=DATEDIF(A2,B2,"d")

    这个函数语法是这样的:=DATEDIF(开始日期,结束日期,第三参数),第三参数可以有若干种情况,分别可以返回间隔的天数、月数和年数。如图所示:

    想要显示的结果有天数,比如:8天,这样的显示效果。可以通过这样实现:在以上的公式后边加上:&"天",=DATEDIF(A13,B13,"d")&"天",就可以了。

    那么,单据计算两个时间短之间的差。怎么实现呢?如图所示:

    对于日期和时间都有的情况下,求相差多少个小时?比如2016年2月10日 13:26到2016年3月6日 10:40相距多少个小时,怎么实现呢?

    输入=(B1-A1)*24,可以实现

    输入=TEXT(B1-A1,"[h]:mm")*24,也可以实现,利用函数text,是为了一个时间格式的转化。

    怎么加上“小时”显示,比如:8小时,这样的显示效果。可以通过这样实现:在以上的公式后边加上:&"小时",与上边讲述的一样。

    =ROUND(TEXT(B4-A4,"[h]:mm")*24,2)&"小时"

    介绍一下函数ROUND( ),为了保存几位小数,上述公式中的"2",就是保留两位小数的意思。

    ROUND(number, num_digits)

    number表示需要进行四舍五入的数值或单元格内容。

    num_digits表示需要取多少位的参数。

    通过学习和咨询,总计了上述的方法,可能显的有些啰嗦,无论怎样,方法和思维对了就可以。还得需要大家详细地具体操作。找到更多的,便捷的方法。

    一起学习和探讨,谢谢

  • ?

    看我72变之日期时间,excel计算时间差不足半小时技巧

    符绿蝶

    展开

    Excel基础操作精选第-5.3-节自定义格式

    在计算员工加班费时,首先要计算的就是加班时间,如果不跨天,直接相减就可以了,可一不小心要是加班到了次日凌晨,直接相减就不太对了,其实并非Excel计算错误,只是需要我们来指定一些特别的格式而已,跟着小编excel小课堂一起看看吧!

    01课题

    数据表格如图中所示,有上班时间和下班时间,在C2输入公式“B2-A2”计算时间差,可结果怎么就变成一个数字呢?

    excel小课堂

    02自定义时间格式h:mm

    结果不对,绝对不是因为小编excel小课堂的公式有问题,也不是excel算的有问题,原因就出在小小的格式设置上,选取C2:C5单元格区域,Ctrl+1快捷键打开设置单元格格式对话框,数字选项卡-分类-自定义,在类型文本框中输入格式代码“h:mm”,点击确定按钮,excel就会以小时:分钟格式来显示加班时间了,别高兴太早,注意看最后一个,明明加班二十多个小时,怎么就只有4小时01分呢。

    03自定义时间格式[h]:mm

    再次强调,绝对不是公式和计算的问题,修改格式代码为[h]:mm,结果正确显示,那么这神奇的h:mm和[h]:mm究竟代表了什么意思呢?

    04日期时间代码

    接下来,小编excel小课堂就为大家科普一下这些个h、mm都起到了什么作用,一切都在此图中

    05日期时间格式案例

    如果加班只计算时间差,那就太开心了,不过这绝对是不可能的,实际中常常会出现一些意外的情况,譬如,计算加班时,如果未超出半个小时,则忽略不计,超过半小时未满1小时,则按半小时计,依次类推,听起来很复杂,其实只要一个公式就可以搞定,就是=FLOOR.MATH((K2-J2)*24,0.5),是不是很厉害呢?

    关注excel小课堂,学好excel,提高职业素养,提升职场竞争力,升职加薪不再是梦!

  • ?

    Excel减法函数公式怎么用与求日期时间差并转为天与小时

    费复天

    展开

    Excel没有直接提供减法函数公式,但可以用求和函数公式和减号做减法运算,因此,Excel减法函数公式主要是指求和函数公式。做减法计算时,通常直接用减号,这样比用求和函数公式书写方便,效果又完全一样。在做减法计算过程中,单纯的数字相减比较容易,比较麻烦的问题做日期时间差,特别是跨小时与跨超过两天的情况,这两种情况若直接用减法常常会得到错误的结果,因此还得用其它辅助方法。有时为便于计算还要把计算结果转天、小时或分钟,做这些转换需用Excel提供的日期时间函数。以下是Excel减法函数公式怎么用以及求日期时间差的具体操作方法,操作中所用版本为 Excel 2016。

    一、Excel用“减号-”做减法运算

    1、假如要求服装表二月销量与一月销量的差。选中用于保存运算结果的 G2 单元格,输入公式 =F2-E2,按回车,则求出两月销量之差;用鼠标按住单元格填充柄,往下拖,则所经过的单元格全用 G2 的值填充,按 Ctrl + S 保存,则所有行变为对应的月销量之差,操作过程步骤,如图1所示:

    图1

    2、在计算结果中出现了负值,这是由于一月销量大于二月销量所致。

    二、Excel减法函数公式怎么用

    1、同样以计算服装表二月销量与一月销量的差为例。在 G2 单元格中输入公式 =SUM(F2,-E2),如图2所示:

    图2

    2、按回车,则求得与用减号一致的结果,如图3所示:

    三、Excel用减法计算日期时间差

    (一)计算结果只保留时分(h:mm)

    1、在 C1 单元格中输入公式 =IF(B1 >A1,B1-A1,24-ABS(B1-A1)),按回车,则求得结果为小数,操作过程步骤,如图4所示:

    图4

    2、选中 C1,按 Ctrl + 1 组合键,打开“设置单元格格式”窗口,选择左边“分类”下面的“自定义”,选择“时分(h:mm)”的时间格式,单击“确定”则 C1 单元格的数字变为 3:13,即 3 小时 13 分,操作过程步骤,如图5所示:

    图5

    提示:A4 的日期时间为 2018/7/28 21:32,B4 的日期时间为 2018/7/31 0:35,两者之间相差 2 天 3 小时 2 分,而计算结果只有 3 小时 2 分,把 2 天丢了,这明显不正确,因此还要把天显示出来,方法看下面的“计算结果保留天和时分(d h:mm)”。

    3、选中 C1,按住单元格填充柄并往下拖,则所经过单元格用 C1 的值填充,如图6所示:

    图6

    4、按 Ctrl + S 组合键保存,则自动计算出剩余行的时间差,如图7所示:

    图7

    (二)计算结果保留天和时分(d h:mm)

    1、选中 C1:C5,按 Ctrl + 1 组合键,打开“设置单元格格式”窗口,选择左边的“自定义”,把 h:mm 改为 d h:mm,单元“确定”,则计算结果变为 天和时分(d h:mm);操作过程步骤,如图8所示:

    图8

    2、如果日期相差超过一个月,则日期格式需要再增加月(m);如果日期相差超过一年,则日期格式需要再增加年(yyyy)。

    (三)把计算结果转为小时

    1、为便于计算,有时需要把两日期差转为小时。在 D1 中输入公式 =SUM(DAY(C1)*24,HOUR(C1),MINUTE(C1)/60),如图9所示:

    图9

    2、按回车,则求出结果 3.216666667 小时;按住单元格填充柄并往下拖,然后按 Ctrl + S 保存,则把剩余行的日期差转为小时,如图10所示:

    图10

    3、公式说明:公式先从 C1 取出天,然后乘以 24 小时,则把天转为小时;然后取出小时;再取出分钟并除以60,以把分钟转为小时;最后把它们加起来。

    四、Excel把表格中的所有数减去一个数

    1、假如要把服装表中的所有数字减 6。选中 G2 单元格,右键 G2,在弹出的菜单中选择“复制”;框选 E2:F8,右键选中的区域,在弹出的菜单中选择“选择性粘贴”,打开该窗口,选择“运算”下的“减”,单击“确定”,则框选的所有数字都减了 6;操作过程步骤,如图11所示:

    图11

    2、无论是乘除还是加减,如果要求把表格中的所有数与某一个数做运算,都可以用选择性粘贴的方法。

  • ?

    Excel中的各种时间差、日期差,该如何计算

    静槐

    展开

    有些朋友被Excel中的时间差计算问题所困扰,所以今天整理了一下各种时间差、日期差的计算方法以及注意事项。

    第一、计算“几小时几分钟几秒”的时间差

    最简单的方法,就是用较大的时间减去减小的时间。所谓时间较大者指的是一天中更靠后的时间。

    公式:=B2-B1或=TEXT(B2-B1,"h:mm:ss")

    注意:原始的时间和计算结果单元格都必须设置成时间格式,如“h:mm:ss”。

    如果你没有将结果单元格设置成时间格式,会得到一个数字(如0.0957);而如果你用较小的时间减去较大的时间,那就会得到一堆的“#”。

    这么说,在计算时间之前,要用眼睛判断哪个大哪个小,然后再计算咯?

    当然不是,我们可以把公式变成下面这两种样子:

    公式1:=B2-B1+IF(B2B2,TEXT(B1-B2,"-h:mm:ss"),TEXT(B2-B1,"+h:mm:ss"))

    咦,这两个公式的计算结果有时候不一样呢?

    但这两个公式都是正确的。当B2的时间数值上比B1小时,如果你用第二个公式,则表示这两个时间属于同一天,如果你用第一个公式,则表示B2的时间是第二天的时间,两者的计算结果相差24小时。

    第二、计算小时差、分钟差和秒数差

    在计算考勤时间时,我们不想得到“几天几小时几分钟几秒”的时间差,希望将时间差转换成小时、分钟或秒。这就可以使用上图的公式。

    注意,原始的时间还是要设置成时间格式,时间差单元格设置成数值格式。用这种方法计算,会默认两个时间属于同一天。

    公式中的1440表示“24小时*60分钟”,86400表示“24小时*60分钟*60秒”。

    计算日期差

    计算日期差,可以使用函数“DATEDIF(开始日期,结束日期、"Y/M/D")”,“Y”表示计算相差几年、“M”表示计算相差几月,“D”表示计算相差几天。

    再次提醒一下,在计算时间差、日期差之前,一定要确保单元格的格式设置正确了。否则,将得到不正确的计算结果。

    学习,为了更好的生活。欢迎点赞、评论、关注和点击头像。

所有视频需要登录后,才能观看

请先登录您的帐号,即可完整播放,如果您尚未注册帐号,请先点击注册。