如何利用EXCEL计算时两列时间差？
关注者
7
被浏览
68,061
5 个回答
题主的需求我不是很明确，是想处理现成的数据并计算加班时间？还是规划以后的数据录入的统一方式？既然搞不清我就从头开始说吧，如有异议请评论区留言，轻喷……
一、Excel计算时间的基本原理
1、日期时间的十进制转换
Excel在应用层始终是以十进制数为基础，日期和时间也是转换为十进制数的统一计算模型。
该算法日期是以天为基本单位，起点设置在1900年1月1日0时0分0秒，此时为1.000000；如果1900年1月2日0时0分0秒，则为2.00000000；以此类推，2020年元旦0点整就是43831.000000。（至于宋元明清的时间咋办，你可以试试，哈哈）
时间则是以小数表示，1s=1/(60*60*24)；1min=1/(60*24)；1h=1/24。
这样就可以将日期时间统一转换成十进制数参与任何计算。
可以验证一下，比如任意单元格输入“1900/1/2 1:00:01”，然后将单元格格式改为常规，你会发现变成2.04167824074074。按上述算法应该是2+3601/(60*60*24)，结果也是2.04167824074074。
2、Execl能识别的日期输入格式
2003版之后的Excel的时间标准录入格式为“yyyy/mm/dd hh:mm:ss”，比如“2020/5/12 19:35:22”，这样Excel会将日期时间识别为十进制数据，才可以参与计算。
其中只录入时间或只录入日期都行，就是只录入时间会被定义为1900年，只录入日期会被定义为0点整。
日期的“/”可以用“-”、“年月日”等替换，时间的冒号“:”好像不能用其它代替，但是12小时制时在时间可以后面跟空格然后接AM或PM，比如“5:30 PM”，也可以识别。
正确输入时间后可以用单元格格式控制时间的表示格式，但如果录入的不符合格式只会识别为文本，比如问题中的“上午 8:30”。
录入正确的话，公示栏显示的始终为标准时间格式，如图：
二、打卡时间的转换
A列中的如果录入时就是“上午 8:30”的方式，那必然是文本格式，如果全列都是“上午”开头，那么可以用函数REPLACE()先替换掉“上午”二字为空值，之后接空格+AM，再用TIMEVALUE()转为数字，再 通过控制单元格格式任意显示12小时或24小时制 。
A列上午时间转换：
=TIMEVALUE(REPLACE(A2,1,2,"")&" AM")
B列下午时间转换：
=TIMEVALUE(REPLACE(B2,1,2,"")&" PM")
还可能存在上下午判断的情况，就用if判断下，很简单：）
=TIMEVALUE(REPLACE(A2,1,2,"")&IF(COUNTIF(A2,"*"&"上午"&"*")>=1," AM"," PM"))
这个公式只要包含“上午”就会判断为AM，否则就是PM。
三、加班时间的计算
这里我只考虑正常5:30下班后的加班时间，不考虑总工作时间即“下班刷卡时间-17:30”
计算加班时间，我看有21点下班的情况，是不是有0点之后下班的情况呢？建议在转换时加入日期。比如在D列加入上班日期（根据实际情况录入或者填0），F列为下班日期，下班日期可以通过下班时间判断，如果出现下班刷卡为上午1:30之类的情况（如图第5行），则下班日期在上班日期基础上加1天：
这样在日期转换里面加上上下班日期的话，就会正确计算加班时间，如图：
计算加班时间，可以直接用转换后的“带日期的下班时间-(上班日期+17:30)”：
=G2-(D2+TIMEVALUE("17:30:00"))
（看见没，半夜下班的社畜大哥已经正确计算加班时间为8小时）
此时，领导让统计一下总加班时间：
表里看是14小时，看上去好像没啥问题，但是过了几天……：
卧槽，亏大了！
此时需要把加班时长转换为十进制，此处以十进制小时为例：
=(G2-(D2+TIMEVALUE("17:30:00")))*24
意思就是在原有基础上乘以24，且H整列的单元格格式设置为常规即可。（见本文第一节算法）。
没有人知道自己到底是谁。
简单地操作即是B减A。取决于AB列数据格式，如果是已经输入好的现成数据，要看一看这数据格式是否可以转化为日期格式。在格式转化里，可以选择24时格式。不过如果原来输入的冒号是中文标点模式，先全部替换为英文标点。还要检查一下原输入里有无不必要的空格，也全部替换掉。
如果刷卡数据直接生成的数据库，可以调整原数据库格式。