EXCEL中时间计算公式
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
今天是 2014-3-13 12:01
Excel 在内部把每 24 小时的时间周期作为一个从 0 至 1的小数存储。所以设公式时就要注意了! 有一段限用(29次)的VBA,如果你对VBA感兴趣的可以研究一下代码!
时间计算一 计算实际加班时数 姓名 A B C D E 上班时间 18:00 18:00 18:00 17:00 18:00 下班时间 21:00:00 22:00:00 23:00:00 22:30:00 20:30:00 计算实际加班时数 3:00 4:00 5:00 5:30 2:30
加班费
￥517.65 ￥678.30 ￥696.15 ￥758.63 ￥580.13 .=C37+D37,直接相加不设"自定义格式"是错误的. .=C37+D37,直接相加(自定义格式[h]:mm,就正确了.
.=(C39+D39)*24*17.85,时间相加后乘以24,相加班费.
平时加班数(转为可计算)
时间计算八 计算实际上班时数(减去休息时间) 姓名 A A B C D E 上班时间 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 下班时间 2005-7-7 11:30 2005-7-8 11:30 2005-7-9 11:30 2005-7-10 11:30 2005-7-11 11:30 2005-7-12 11:30 上班时间 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00
时间计算十(新) "分钟格式"转为"时间格式" 姓名 A A B C D E 时间数
30.6分钟 40.1分钟 50.4分钟 35.3分钟 20.4分钟 22.3分钟
时间格式显示 0:30:36 0:40:06 0:50:24 0:35:18 0:20:24 0:22:18
公式一 .=TIME(,LEFT(C138,4),MOD(LEFT(C138,
34 34 34 34 34 34
094:30 094:30 097:30 084:00 095:00 130:00
平时加班 1730 1830 2030 1730 1730 1730
当日实得薪金 142.50 157.50 187.50 142.50 142.50 142.50
时间计算五 用时间计算天数 姓名 A A B C D E 周日加班 8:00 8:00 16:00 16:00 20:00 12:00 平时加班 21:00 21:00 22:00 23:00 22:30 20:30 计算实际加班时数 29:00 29:00 38:00 39:00 42:30 32:30
时间计算九(新) 计算实际秒数(无日期) 姓名 A A B C D E 开始时间
23:15:00 0:15:01 0:21:45 1:05:00 4:02:01 3:02:01
结束时间
0:15:00 0:20:12 0:45:31 4:05:16 4:07:00 3:07:00
实际(秒数)时间
3600.00 311.00 1426.00 10816.00 299.00 299.00
姓名 A A B C D E
时间数
30.6分钟 40.1分钟 50.4分钟 35.3分钟 20.4分钟 22.3分钟
时间格式显示 00:30:36 00:40:06 00:50:24 00:35:18 00:20:24 00:22:18
公式二 .=TEXT(LEFT(C138,4)/1440,"hh:mm:ss"
显第几周 28 33 38 42 47 51
3-13 12:01
存储。所以设公式时就要注意了!
公式:=C5-B5,直接相减就可以了
加班费 5:33
￥71.40 ￥89.25 ￥98.18 ￥44.63
直接相乘计算结果不正确 (5:33) .=(SUM(HOUR(SUM(D24,-C24)),MINUTE(SUM(D24,-C24))/60))*17.85
时间计算六 无规律性的时间计算
姓名 周日加班 平时加班 转换时间格式(周日)
A A
080000 100000
210000 210001
8:00:00 10:00:00
B C D E
160000 170000 200000 120000
220000 230000 243000 203000
16:00:00 17:00:00 20:00:00 12:00:00
时间计算四(新) 计算文本格式的时间(一)
姓名 周日加班(文本) 周日加班(转为可计算) 平時加班(文本)
A 034:00 A 034:00 B 034:00 C 034:00 D 034:00 E 034:00 新 计算文本格式的时间(二),假设每小时15元计 姓名 A A B C D E 周日加班 0730 0730 0730 0730 0730 0730
合计
94.5 94.5 97.5 84 95 130
128.5 128.5 131.5 118 129 164
.=VALUE(LEFT(C52,3))+VALUE(RIGHT(C52,2))/60 单元格格式设为常规即可
合计实际天数 3.63 3.63 4.75 4.88 5.31 4.06
.=E62*24/8
时间计算三 计算加班费, 每小时加班费17.85元 姓名 A A B C D E 周日加班 8:00 8:00 16:00 16:00 20:00 12:00 平时加班 21:00 21:00 22:00 23:00 22:30 20:30 计算实际加班时数 5:00 29:00 38:00 39:00 42:30 32:30
提取年份 提取月份 提取日期 提取分钟
.=SECOND(B170) 提取秒数 .=WEEKDAY(B170)显示星期 .=WEEKNUM(B160)显示第几周
E108,F108,"d"))*20
38:00:00 40:00:00 20:30:00 32:30:00
相隔天数
151 59 35 90 28 31
相隔时间 3637.0 .=DAYS360(C92,E92) 1429.0 .=(E92-C92)*24 846.0 2167.0 674.5 745.0
下班时间 求D107-D112列实际时数 求E107-E112列实际时数 4.00 4.00 .=(F107-E107)*24-(DATEDIF(E107,F107,"d"))*20 2005-7-7 17:00 8.00 8.00 2005-7-8 17:00 .=(F108-E108)*24-(DATEDIF(E108,F108,"d"))*20 12.00 15.50 2005-7-9 20:30 16.00 22.00 2005-7-10 23:00 20.00 25.50 2005-7-11 22:30 24.00 30.50 2005-7-12 23:30
转换时间格式(平时)
合计
Байду номын сангаас
21:00:00 21:00:01
29:00:00 .=TIME(LEFT(C76,2),MID(C76,3,2),RIGHT(C76,2)) 31:00:01 .=TIME(LEFT(D76,2),MID(D76,3,2),RIGHT(D76,2))
22:00:00 23:00:00 0:30:00 20:30:00
时间计算二 计算加班费, 每小时加班费17.85元 姓名 A B C D E 上班时间 18:00 18:00 18:00 17:00 18:00 下班时间 21:00:00 22:00:00 23:00:00 22:30:00 20:30:00 计算实际加班时数 3:00 4:00 5:00 5:30 2:30
日
期
2005-7-9 12:10:59 2005-8-10 10:20:03 2005-9-11 8:30:15 2005-10-13 15:40:22 2005-11-14 3:05:31 2005-12-16 14:02:45
只需要秒数 59 3 15 22 31 45
显示星期 七 四 一 五 二 六
时间计算七 计算相隔时间 姓名 A A B C D E 开始时间 2005-1-1 8:00 2005-1-2 8:00 2005-1-1 16:00 2005-1-3 16:00 2005-2-1 20:00 2005-3-1 12:00 结束时间 2005-6-1 21:00 2005-3-2 21:00 2005-2-5 22:00 2005-4-3 23:00 2005-3-1 22:30 2005-4-1 13:00 相隔月份 5 2 2 3 1 1
时间计算十一(新) 提取日期/时间的部分值 日 期 只需要年份 2005 2005 2005 2005 2005 2005 只需要月份 7 8 9 10 11 12 只需要日期 9 10 11 13 14 16
2005-7-9 12:10 2005-8-10 10:20 2005-9-11 8:30 2005-10-13 15:40 2005-11-14 3:05 2005-12-16 14:02
.=IF(C121>D121,(D121+1-C121)*60*60*24,(D121-C121)*60*60*24)
,LEFT(C138,4),MOD(LEFT(C138,4),1)*60)
LEFT(C138,4)/1440,"hh:mm:ss")
只需要时间 12 10 8 15 3 14
只需要分钟 10 .=YEAR(B160) 20 .=MONTH(B160) 30 .=DAY(B160) 40 .=HOUR(B160) 5 2
Excel 在内部把每 24 小时的时间周期作为一个从 0 至 1的小数存储。所以设公式时就要注意了! 有一段限用(29次)的VBA,如果你对VBA感兴趣的可以研究一下代码!
时间计算一 计算实际加班时数 姓名 A B C D E 上班时间 18:00 18:00 18:00 17:00 18:00 下班时间 21:00:00 22:00:00 23:00:00 22:30:00 20:30:00 计算实际加班时数 3:00 4:00 5:00 5:30 2:30
加班费
￥517.65 ￥678.30 ￥696.15 ￥758.63 ￥580.13 .=C37+D37,直接相加不设"自定义格式"是错误的. .=C37+D37,直接相加(自定义格式[h]:mm,就正确了.
.=(C39+D39)*24*17.85,时间相加后乘以24,相加班费.
平时加班数(转为可计算)
时间计算八 计算实际上班时数(减去休息时间) 姓名 A A B C D E 上班时间 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 2005-7-7 7:30 下班时间 2005-7-7 11:30 2005-7-8 11:30 2005-7-9 11:30 2005-7-10 11:30 2005-7-11 11:30 2005-7-12 11:30 上班时间 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00 2005-7-7 13:00
时间计算十(新) "分钟格式"转为"时间格式" 姓名 A A B C D E 时间数
30.6分钟 40.1分钟 50.4分钟 35.3分钟 20.4分钟 22.3分钟
时间格式显示 0:30:36 0:40:06 0:50:24 0:35:18 0:20:24 0:22:18
公式一 .=TIME(,LEFT(C138,4),MOD(LEFT(C138,
34 34 34 34 34 34
094:30 094:30 097:30 084:00 095:00 130:00
平时加班 1730 1830 2030 1730 1730 1730
当日实得薪金 142.50 157.50 187.50 142.50 142.50 142.50
时间计算五 用时间计算天数 姓名 A A B C D E 周日加班 8:00 8:00 16:00 16:00 20:00 12:00 平时加班 21:00 21:00 22:00 23:00 22:30 20:30 计算实际加班时数 29:00 29:00 38:00 39:00 42:30 32:30
时间计算九(新) 计算实际秒数(无日期) 姓名 A A B C D E 开始时间
23:15:00 0:15:01 0:21:45 1:05:00 4:02:01 3:02:01
结束时间
0:15:00 0:20:12 0:45:31 4:05:16 4:07:00 3:07:00
实际(秒数)时间
3600.00 311.00 1426.00 10816.00 299.00 299.00
姓名 A A B C D E
时间数
30.6分钟 40.1分钟 50.4分钟 35.3分钟 20.4分钟 22.3分钟
时间格式显示 00:30:36 00:40:06 00:50:24 00:35:18 00:20:24 00:22:18
公式二 .=TEXT(LEFT(C138,4)/1440,"hh:mm:ss"
显第几周 28 33 38 42 47 51
3-13 12:01
存储。所以设公式时就要注意了!
公式:=C5-B5,直接相减就可以了
加班费 5:33
￥71.40 ￥89.25 ￥98.18 ￥44.63
直接相乘计算结果不正确 (5:33) .=(SUM(HOUR(SUM(D24,-C24)),MINUTE(SUM(D24,-C24))/60))*17.85
时间计算六 无规律性的时间计算
姓名 周日加班 平时加班 转换时间格式(周日)
A A
080000 100000
210000 210001
8:00:00 10:00:00
B C D E
160000 170000 200000 120000
220000 230000 243000 203000
16:00:00 17:00:00 20:00:00 12:00:00
时间计算四(新) 计算文本格式的时间(一)
姓名 周日加班(文本) 周日加班(转为可计算) 平時加班(文本)
A 034:00 A 034:00 B 034:00 C 034:00 D 034:00 E 034:00 新 计算文本格式的时间(二),假设每小时15元计 姓名 A A B C D E 周日加班 0730 0730 0730 0730 0730 0730
合计
94.5 94.5 97.5 84 95 130
128.5 128.5 131.5 118 129 164
.=VALUE(LEFT(C52,3))+VALUE(RIGHT(C52,2))/60 单元格格式设为常规即可
合计实际天数 3.63 3.63 4.75 4.88 5.31 4.06
.=E62*24/8
时间计算三 计算加班费, 每小时加班费17.85元 姓名 A A B C D E 周日加班 8:00 8:00 16:00 16:00 20:00 12:00 平时加班 21:00 21:00 22:00 23:00 22:30 20:30 计算实际加班时数 5:00 29:00 38:00 39:00 42:30 32:30
提取年份 提取月份 提取日期 提取分钟
.=SECOND(B170) 提取秒数 .=WEEKDAY(B170)显示星期 .=WEEKNUM(B160)显示第几周
E108,F108,"d"))*20
38:00:00 40:00:00 20:30:00 32:30:00
相隔天数
151 59 35 90 28 31
相隔时间 3637.0 .=DAYS360(C92,E92) 1429.0 .=(E92-C92)*24 846.0 2167.0 674.5 745.0
下班时间 求D107-D112列实际时数 求E107-E112列实际时数 4.00 4.00 .=(F107-E107)*24-(DATEDIF(E107,F107,"d"))*20 2005-7-7 17:00 8.00 8.00 2005-7-8 17:00 .=(F108-E108)*24-(DATEDIF(E108,F108,"d"))*20 12.00 15.50 2005-7-9 20:30 16.00 22.00 2005-7-10 23:00 20.00 25.50 2005-7-11 22:30 24.00 30.50 2005-7-12 23:30
转换时间格式(平时)
合计
Байду номын сангаас
21:00:00 21:00:01
29:00:00 .=TIME(LEFT(C76,2),MID(C76,3,2),RIGHT(C76,2)) 31:00:01 .=TIME(LEFT(D76,2),MID(D76,3,2),RIGHT(D76,2))
22:00:00 23:00:00 0:30:00 20:30:00
时间计算二 计算加班费, 每小时加班费17.85元 姓名 A B C D E 上班时间 18:00 18:00 18:00 17:00 18:00 下班时间 21:00:00 22:00:00 23:00:00 22:30:00 20:30:00 计算实际加班时数 3:00 4:00 5:00 5:30 2:30
日
期
2005-7-9 12:10:59 2005-8-10 10:20:03 2005-9-11 8:30:15 2005-10-13 15:40:22 2005-11-14 3:05:31 2005-12-16 14:02:45
只需要秒数 59 3 15 22 31 45
显示星期 七 四 一 五 二 六
时间计算七 计算相隔时间 姓名 A A B C D E 开始时间 2005-1-1 8:00 2005-1-2 8:00 2005-1-1 16:00 2005-1-3 16:00 2005-2-1 20:00 2005-3-1 12:00 结束时间 2005-6-1 21:00 2005-3-2 21:00 2005-2-5 22:00 2005-4-3 23:00 2005-3-1 22:30 2005-4-1 13:00 相隔月份 5 2 2 3 1 1
时间计算十一(新) 提取日期/时间的部分值 日 期 只需要年份 2005 2005 2005 2005 2005 2005 只需要月份 7 8 9 10 11 12 只需要日期 9 10 11 13 14 16
2005-7-9 12:10 2005-8-10 10:20 2005-9-11 8:30 2005-10-13 15:40 2005-11-14 3:05 2005-12-16 14:02
.=IF(C121>D121,(D121+1-C121)*60*60*24,(D121-C121)*60*60*24)
,LEFT(C138,4),MOD(LEFT(C138,4),1)*60)
LEFT(C138,4)/1440,"hh:mm:ss")
只需要时间 12 10 8 15 3 14
只需要分钟 10 .=YEAR(B160) 20 .=MONTH(B160) 30 .=DAY(B160) 40 .=HOUR(B160) 5 2