中企动力 > 商学院 > excel小时转换分钟
  • ?

    你还在为excel表格中的数据格式转换而烦恼吗?学会这一招,效率瞬间提升10倍以上!

    董含卉

    展开

    前言:

    最近有同学在后台向我提出excel格式转换的问题,希望能得到解答。我觉得这个问题很有普遍意义,可能对其他同学也有参考价值,故抽出时间写了以下的教程,希望能给大家的工作带来帮助和启发。

    问题如下:有各种格式组成的excel,有数值,年月日,字符串等,每列数据的左前方有个逗号,这个逗号是隐藏的,双击数据才能看到,现在想去掉这个逗号(注:即转为数据或日期等格式。本身是文本的不用转换,左前方有个逗号没有影响),有什么好办法吗?分列我有用过,我的数据有多达42列,分列只能一列一列处理,太费劲,能有统一处理的快捷办法吗?我几乎每天都要处理同样的excel,都是同样的格式。具体原表数据如下方的模拟演示:

    以文本的形式的存在的原数据

    解决方案:

    1、借助函数处理数据:

    在终表的A1单元格输入以下公式,向下及向右拖动公式覆盖原表的单元格区域。再将终表全选,选择性粘贴为数据,再手工调整日期所在列为日期格式即可。

    =IFERROR(原表!A1*1,原表!A1)

    公式解读:

    原表!A1*1,代表将文本转为数值,日期是一种特殊的数据。但如果文本里无数据会出错。

    IFERROR(原表!A1*1,原表!A1),代表如果出错,即等于原表!A1本身的值。本身是字符串还是字符串,不需要转换。

    借助函数处理数据

    2、借助选择性粘贴:

    先复制数据:1,再选中所有需转换的区域,点选择性粘贴,点击:运算-乘,确定后,即可以得到我们想要的数据。,再手工调整日期所在列为日期格式即可。

    借助选择性粘贴

    结语:数据格式转换其实很简单,学会这一招,效率瞬间提升10倍以上!还不快来试试看!

    致力于提供好玩又有趣的excel原创教程

  • ?

    EXCEL日期和时间函数的使用方法

    夜已深

    展开

    ● 函数分类

    ▼表 1-1 返回当前的日期和时间以及指定的日期和时间

    函数名称 功能

    DATE 返回指定的日期的序列号

    NOW 返回日期时间格式的当前日期和时间

    TODAY 返回当前日期

    TIME 将制定内容显示为一个时间

    ▼表 1-2 返回日期和时间的某个部分

    函数名称 功能

    DAY 返回日期中具体的某一天

    HOUR 返回小时数

    MONTH 返回月份

    MINUTE 返回分钟数

    SECOND 返回秒数

    YEAR 返回年份

    WEEKDAY 返回当前日期是星期几

    01 日期和时间函数 日期和时间函数是用来计算日期和时间,或设置日期和时

    间的格式的函数,例如"计算员工工龄"。Excel 2013提供了 24

    个日期和时间函数,本章将详细介绍日期和时间函数的基本用

    法及函数的实际工作中的应用。

    ▼表 1-3 文本与日期、时间格式间的转换

    函数名称 功能

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

    TIMEVALUE 将指定日期的序列号转换为文本日期

    ▼表 1-4 其他日期函数

    函数名称 功能

    DAYS 计算两个日期之间的天数

    DAY360 以 360天为准计算两个日期间天数

    EDATE 计算从起始日期向前或向后几个月的日期

    的序列号

    EOMONTH 计算从起始日期向前或向后几个月的月份

    的最后一天的序列号

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

    NETWORKDAYS 计算日期间的所有工作数

    NETWORKDAYS.INTL 计算日期间的所有工作日数,使用参数指明

    周末的日期和天数

    WORKDAY 计算与指定日期相隔数个工作日的日期

    WORKDAY.INTL 计算与制定日期相隔数个工作日的日期,使

    用参数指明周末的日期和天数

    WEEKNUM 返回日期在一年中是第几周

    YEARFRAC 计算从起始日期到终止日期所经历的天数

    占全年天数的百分比

    使用 YEAR和MONTH函数提取当前日期的年份和月份,并

    将月份加 1,日部分设置为 0,表示下个月的第 0天,即当前月份

    的最后一天。最后使用 TEXT 函数将结果设置为以阿拉伯数字表

    示的本月的天数。

    DATE

    返回指定日期的序列号

    特定日期

    的序列号

    函数格式: DATE(year,month,day)

    参数说明: year(必选):指定年份或者年份所在的单元格。

    month(必选):指定月份或者月份所在的单元格。

    day (必选):指定日或者日所在的单元格。

    注意事项: (1)所有参数可以是直接输入的数字或单元格引用。

    (2)所有参数都必须为数值类型,即数字、文本格式的数

    字或表达式。如果是文本,则返回错误值#VALUE!。

    (3)参数 year的值必须在 1900~9999之间,如果大于 9999,

    则返回错误值#VALUE!。参数 month和 day不同,month的正

    常范围是 1~12,day的正常范围是 1~31。

    (4)DATE函数对月和日有自动更正功能。如果月大于 12,

    那么 Excel会自动转换到下一年;如果日大于 31,Excel会将其

    转换到下一个月。同理,如果月和日都小于 1,则 Excel会将其

    转换到上一年或上一个月。

    案例 计算本月的天数

    使用 DATE 函数时还可以把公式作为函数的参数。例如:利

    用DATE函数来求 1个月后的日期,此时需要在month参数上加 1。

    在 单 元 格 B1 中 输 入 公 式 =TEXT

    (DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

    ,"d"),并按下【Enter】键。

    案例 显示 1 个月后的日期

    ①在单元格 B4 中输入公式

    =DATE($A$1,$A$2+1,A4),并按

    下【Enter】键。

    ②向下复制公式,计算其他

    单元格的值。

    NOW

    返回日期时间格式的当前日期和时间

    当前

    日期

    函数格式: NOW()

    参数说明: 不需要参数,但必须有()。如果括号中输入参数,则会返

    回错误值。

    使用 NOW 函数返回当前日期,然后使用 TEXT 函数将当前

    日期设为"月-日"格式。再使用 10月 1日减去当前日期,然后使用

    TEXT函数将差值设置为数字格式,即日期序列号。最后加 1即可

    得到当前日期距离 10月 1日的天数。

    输入 NOW 函数,显示当前日期和时间。如果函数所在的单

    元格格式为"常规",则 Excel显示如"2014/8/4 14:00"的日期格式。

    用户也可以根据需要重新设置单元格格式。

    注意事项: (1)NOW函数返回的是 Windows系统中设置的日期和时

    间。

    (2)NOW 函数返回的日期和时间不会实时更新,除非工

    作表被重新计算。

    (3)在格式为"常规"的单元格中使用 NOW函数时,返回

    以正常的日期格式显示的当前日期和时间。如果需要显示当前

    日期对应的序列号,需将单元格格式设置为"常规"。也可以使

    用 TEXT函数强制单元格中的日期显示为序列号。

    案例 十一倒计时

    在 单 元 格 B1 中 输 入 公 式

    =TEXT("10-1"-TEXT(NOW(),"mm-dd"),"0")+1

    ,并按下【Enter】键。

    案例 显示当前日期和时间

    如果员工的"离职日期"对应列的单元格为空,说明员工未

    离职,则计算该员工从入职日期到当前日期的天数;如果不为空,

    说明员工已经离职,则使用离职日期减去入职日期。

    在单元格 B1 中输入公式 NOW()

    并按下【Enter】键。

    案例 计算员工在职时间

    在 单 元 格 G2 中 输 入 公 式

    =ROUND(IF(F2<>"",F2-E2,NOW(

    )-B2),0)并按下【Enter】键。

    TODAY

    返回当前日期

    当前

    日期

    函数格式: TODAY()

    参数说明: 不需要参数。

    注意事项: (1)TODAY函数返回的是Windows系统中设置的日期。

    (2)TODAY 函数返回的日期不会实时更新,除非工作表

    被重新计算。

    如果已知某人的身份证号,则可以使用MID函数提取其出生

    年份,然后使用 TODAY 函数计算其年龄,使用 YEAR函数返回

    年数。

    新员工入职后,一般都要先进行试用。本案例以试用期为 1

    个月即 30天为例,统计新入职的员工试用期到期的人数。首先试

    用 TOADY 函数获得当前日期,然后减去 30,再与员工的入职时

    间进行比较。如果入职时间大,则说明该员工试用期已经到期,

    然后使用 COUNTIF函数统计符合条件的个数即可。

    (3)在格式为"常规"的单元格中使用 TODAY 函数时,返

    回以正常的日期格式显示的当前日期。如果需要显示当前日期

    对应的序列号,需将单元格格式设置为"常规"。

    案例 计算年龄

    在单元格 F2 中输入公式

    =YEAR(TODAY())-MID(C2,

    7,4),并按下【Enter】键。

    案例 统计试用期到期的人数

    在单元格 C2 中输入公式

    =COUNTIF(C3:C12,"<"&TOD

    AY()-90),并按下【Enter】

    键。

    本案例以安排会议时间为例,首先使用 TEXT(NOW())获

    得格式化后的当前时间,然后加上时间间隔,例如 1 个半小时,

    此时间间隔可由 TIME函数得到,最后计算出准确的会议时间。

    TIME

    返回指定时间的序列号

    特定日期

    的序列号

    函数格式: TIME(hour,minute,second)

    参数说明: hour(必选):表示小时。0(零)到 32767 之间的数值。

    任何大于 23 的数值将除以 24,其余数将视为小时。

    minute(必选):表示分钟。0 到 32767 之间的数值。任

    何大于 59 的数值将被转换为小时和分钟。

    second(必选):表示秒。0 到 32767 之间的数值。任何

    大于 59 的数值将被转换为小时、分钟和秒。

    注意事项: (1)所有参数可以是直接输入的数字或单元格引用。

    (2)所有参数都必须为数值类型,即数字、文本格式的数

    字或表达式。如果是文本,则返回错误值#VALUE!。

    (3)如果在输入函数前,单元格的格式为"常规",则结果

    将设为日期格式。

    案例 安排会议时间

    使用 TIME 函数,将输入在各个单元格内的时、分、秒合为

    一个数值。

    效果如下图所示。

    在 单 元 格 B1 中 输 入 公 式 =TEXT

    (NOW(),"hh:mm")+TIME(1,30,0) , 并 按 下

    【Enter】键。

    案例 返回指定时间的序列号

    ①在单元格 D2 中插入

    TIME函数。

    ②单击要输入函数的

    单元格。

    ③指定参数,然后单击

    "确定"按钮。

    10

    使用 DAY函数提取"日"。

    DAY

    返回日期中具体的某一天

    用序列号

    表示日期

    函数格式: DAY(serial_number)

    参数说明: serial_number:表示要查找的天数日期。日期有多种输入

    方式:带引号的文本串(例如 "1988/01/30")、系列数(例如,

    如果使用 1900 日期系统则 35825 表示 1998 年 1 月 30

    日 ) 或 其 他 公 式 或 函 数 的 结 果 ( 例 如

    DATEVALUE("1998/1/30"))。

    注意事项: (1)使用 DAY 函数,只显示日期值或表示日期文本的天

    数,返回值为 1~31间的整数。

    (2)serial_number表示的日期应该以标准的日期格式输入,

    或者使用 DATE、NOW、TODAY 等函数输入。如果日期以非

    标准日期格式的文本形式输入,DAY 函数将返回错误值

    #VALUE!。

    案例 提取"日"

    ①单击"插入函数"按钮。

    11

    提取结果如下。

    公司每天都有其销售记录,并需定期对销售情况进行分析。

    本案例以统计本月上旬销售总额为例,首先使用 DAY 函数提取

    "日",然后判断是否小于 11。如果小于 11,则该日期为本月上旬,

    然后返回该日期的销售额,最后使用 SUM 函数对返回的数组求

    和。

    ②在弹出的"插入函

    数"对话框中选择 DAY

    函数,然后单击"确

    定"按钮。

    ③指定参数,然后单击

    "确定"按钮。

    案例 计算本月上旬销售总额

    12

    使用 HOUR函数提取"小时"。

    在单元格 C1中输入公式

    "=SUM(IF(DAY(A3:A27

    )<11,B3:B27)),然后按

    下【Ctrl+Shift+Enter】

    组合键。

    HOUR

    返回小时数

    用序列号

    表示日期

    函数格式: HOUR(serial_number)

    参数说明: serial_number:表示要提取小时数的时间,可以是表示时

    间的序列号、时间文本或单元格引用。

    注意事项: (1)使用 HOUR函数只显示日期值或表示日期的文本的小

    时数。返回值是 0~23间的整数。

    (2)serial_number 参数必须为数值类型,即数字、文本格

    式的数字或表达式。如果是文本,则返回错误值#VALUE!。

    (3)如果小时超过 24,则 HOUR 函数将提取实际小时与

    24 的差值。例如,如果时间的小时部分为 29,那么 HOUR 函

    数提取小时的返回值为 5。

    案例 提取"小时"

    13

    提取结果如下。

    有些公司使用的是 24小时工作制,假设从早上 8点到晚上 20

    点为白班时间,其他为夜班时间。本案例首先使用 HOUR函数返

    回时间中的小时数;然后使用 AND函数来判断 HOUR函数返回

    ①单击"插入函数"按钮。

    ②在弹出的"插入函

    数"对话框中选择 HOUR

    函数,然后单击"确

    定"按钮。

    ③指定参数,然后单击

    "确定"按钮。

    案例 工作排班

    14

    的数值是否符合条件,如果两个条件均符合,则 AND函数返回值

    为 TRUE,否则返回 FALSE;最后通过 IF函数判断,如果时间段在

    8点到 20点,则条件为真,输出"白班",否则输出"夜班"。

    ①在单元格 C2中输入公

    式 " =IF(AND(HOUR

    (B2)>=8.5,HOUR(B2)<=

    20.5)," 白 班 "," 夜 班

    ")",然后按下 Enter

    键。

    ②使用填充柄向下填充。

    MONTH

    返回月份

    用序列号

    表示日期

    函数格式: MONTH(serial_number)

    参数说明: serial_number:表示要提取月份的日期,可以是表示日期

    的序列号、日期文本或单元格引用。

    注意事项: (1)使用MONTH函数只显示日期值或表示日期的文本的

    月份。返回值是 1~12间的整数。

    (2)参数 serial_number表示的日期应该以标准的日期格式

    输入,或者用 DATE、NOW、TODAY 等函数输入。如果日期

    以非标准日期格式的文本形式输入,MONTH 函数将返回错误

    值#VALUE!。

    15

    使用MONTH函数提取"月"。

    提取结果如下。

    案例 提取"月"

    ①单击"插入函数"按钮。

    ②在弹出的"插入函

    数 " 对 话 框 中 选 择

    MONTH 函数,然后单击

    "确定"按钮。

    ③指定参数,然后单击

    "确定"按钮。

    16

    使用MONTH函数返回日期中的小时数,然后通过 IF函数判

    断,如果条件为真,输出"√",否则输出空白。

    首先使用 YEAR 函数提取单元格 A2 中的年份,然后使用

    DATE 函数将该年份和 2、29 组合为一个日期,即今年的 2 月 29

    日。闰年 2月有 29天,非闰年 2月只有 28天,利用 DATE函数

    的自动更正日期错误功能,并使用 MONTH 函数提取 DATE 函数

    产生的日期中的月份。如果是闰年,提取出的月份就等于 2;如果

    不是,DATE函数会将 2月 29日自动进位到 3月 1日。最后通过

    IF函数判断,如果条件为真,输出"是",否则输出"不是"。

    案例 标记 7...

  • ?

    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中3秒搞定计量单位快速转换

    改变

    展开

    各位会计、出纳人员在日常工作中我们统计的原始数据大多都是以元为计量单位的(如下图):

    而这样的数据在实际工作中则经常需要转换计量单位,比如上图中的数据要转换成以“万元”为计量单位,结果如下图:

    数据少的情况下,我们一个一个手工修改还可以,但是如果数据多,或者经常需要转换的话如何能快速实现呢,不要着急,只需三秒钟,来看一下具体方法吧。

    步骤一:在任意一个空白单元格输入数值10000(以“万元”计量),然后复制该单元格,如下图:

    步骤二:选中需要转换的数据,点击右键选择“选择性粘贴”,在弹出的对话框中选择“运算”中的“除”就可以了,如下图:

    按照我们的使用习惯,转换后的数据保留两位小数就可以了哦,最终效果如下图:

    好了,你get到了没?如果你正好遇到这种情况,赶紧按照这种操作快试试吧!

  • ?

    最实用的50个Excel函数,5分钟可以做2小时的表格!

    好心

    展开

    Excel表格技巧怎么样,直接关系到你的工作效率,当你再为一个函数苦恼时,别人可能已经做好了。接下来整理了50个函数公式,让你一天的表格10分完成。

    1、函数基本统计共有17个

    2、处理文本函数共有14个

    3、函数转换共有20个

    4、关于IF函数的图解

    5、PDF转Excel转换(批量转换也很简单)

    如下图左边,在CAJ转换成Word转换器中,不仅PDF转Excel的操作,还可以将CAJ转Wrod、CAJ转PDF、PDF转Word、PDF转PPT、PDF转Html等操作。

    以上就是为大家分享的Excel的函数技巧了,我会持续给大家整理更多实用性的技巧,帮助新手快速成为准职业人,成为职场上的精英。

  • ?

    EXCEL快速转换日期时间的方法

    Yvonne

    展开

    第一种快速录入当前日期;不需要公式,直接选中单元格,按下“ctrl+;”分号,可以显示当前日期。注意;分号必须在英文状态下。

    第二种快速录入当前时间;同时按下“ctrl+shift+;”按下回车键,显示当前时间。

    第三种使用公式录入日期;选中单元格,输入公式“=now()”按下回车键。公式字母不区分大小写。

    第四种,让时间显示秒数方法,在时间单元格上右击——设置单元格格式——数字——自写义——选择类型,或者直接输入“yyyy-m-d h:mm:ss”——确定。

    设置完成之后,当前时间就能显示秒数。

    完成秒数显示

  • ?

    三个小时,整理出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中的各种时间差、日期差,该如何计算

    雪旋

    展开

    有些朋友被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”表示计算相差几天。

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

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

  • ?

    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秒转换为时分秒的三种公式写法以及公式解析

    Otis

    展开

    Excel中时间相关的函数和问题比较多。要想玩转这些时间函数,做时间的朋友,就得先了解认识它。

    比如常见的Excel中将秒数转换为几分几秒、几天几时几分几秒这样的形式,公式写法就有多样。不妨我们一起来看看。

    第一,Excel秒转换为时分秒的三种方法

    C2单元格公式:=TEXT(A2/86400,"m分ss秒")

    解释:由于A列是秒数,秒转成数值,1天的数值是1,一天24小时,1小时60分钟,1分钟60秒,=1/(1*24*60*60)是1秒的数值,也就是1/86400是1秒的数值。然后再用Text函数将数值显示为分和秒。

    D2单元格公式:=INT(A2/60)&"分"&MOD(A2,60)&"秒"

    解释:使用INT函数对秒数除以60的结果取整,得到分钟数。再使用MOD函数求得秒数对60的余数,得到不足1分钟的剩余秒数。最后使用“&”符号进行函数与文本的连接。

    E2单元格公式:=TEXT(TIME(,,A2),"m分ss秒")

    解释:time函数返回一个时间,它有3个参数,第1参数:时;第2参数:分钟;第3参数:秒。通过teim函数得到一个时间TIME(,,A2),最后用text显示成分和秒。

    第二,秒转换成几天几时几分几秒等形式写法

    G2单元格公式:=TEXT(A2/86400,"hh:mm:ss")

    H2单元格公式:=TEXT(A2/86400,"d天h小时m分钟s秒")

    I2单元格公式:=TEXT(A2/86400,"h小时m分钟s秒")

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

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