EXCEL中时间计算公式

,,,今天是,2005/7/814:53,,,,

,Excel在内部把每24小时的时间周期作为一个从0至1的小数存储。所以设公式时就要注意了!,,,,,,,

,"有一段限用(29次)的VBA,如果你对VBA感兴趣的可以研究一下代码!",,,,,,,

,,,,,,,,,

,,,,,,,,,

,时间计算一,,,,,,,

,,,,,,,,,

,计算实际加班时数,,,,,,,

,,,,,,,,,

,姓名,上班时间,下班时间,计算实际加班时数,,,,

,A,18:00,21:00:00,3:00,"公式:=C5-B5,直接相减就可以了",,,

,B,18:00,22:00:00,4:00,,,,

,C,18:00,23:00:00,5:00,,,,

,D,17:00,22:30:00,5:30,,,,

,E,18:00,20:30:00,2:30,,,,

,,,,,,,,,

,,,,,,,,,

,时间计算二,,,,,,,

,,,,,,,,,

,"计算加班费,每小时加班费17.85元",,,,,,,

,,,,,,,,,

,姓名,上班时间,下班时间,计算实际加班时数,加班费,,,

,A,18:00,21:00:00,3:00,5:33,直接相乘计算结果不正确(5:33),,

,B,18:00,22:00:00,4:00,￥71.40,".=(SUM(HOUR(SUM(D24,-C24)),MINUTE(SUM(D24,-C24))/60))*17.85",,

,C,18:00,23:00:00,5:00,￥89.25,,,

,D,17:00,22:30:00,5:30,￥98.18,,,

,E,18:00,20:30:00,2:30,￥44.63,,,

,时间计算三,,,,,,,

,,,,,,,,,

,"计算加班费,每小时加班费17.85元",,,,,,,

,姓名,周日加班,平时加班,计算实际加班时数,加班费,,,

,A,8:00,21:00,5:00,,".=C37+D37,直接相加不设""自定义格式""是错误的.",,

,A,8:00,21:00,29:00,￥517.65,".=C37+D37,直接相加(自定义格式[h]:mm,就正确了.",,

,B,16:00,22:00,38:00,￥678.30,".=(C39+D39)*24*17.85,时间相加后乘以24,相加班费.",,

,C,16:00,23:00,39:00,￥696.15,,,

,D,20:00,22:30,42:30,￥758.63,,,

,E,12:00,20:30,32:30,￥580.13,,,

,时间计算四(新),,,,,,,

,,,,,,,,,

,计算文本格式的时间(一),,,,,,,

,姓名,周日加班(文本),周日加班(转为可计算),平時加班(文本),平时加班数(转为可计算),合计,,

,A,034:00,34,094:30,94.5,128.5,".=VALUE(LEFT(C52,3))+VALUE(RIGHT(C52,2))/60",

,A,034:00,34,094:30,94.5,128.5,单元格格式设为常规即可,

,B,034:00,34,097:30,97.5,131.5,,

,C,034:00,34,084:00,84,118,,

,D,034:00,34,095:00,95,129,,

,E,034:00,34,130:00,130,164,,

,新,,,,,,,

,"计算文本格式的时间(二),假设每小时15元计",,,,,,,

,,,,,,,,,

,姓名,周日加班,平时加班,当日实得薪金,,,,

,A,0730,1730,142.50,,,,

,A,0730,1830,157.50,,,,

,B,0730,2030,187.50,,,,

,C,0730,1730,142.50,,,,

,D,0730,1730,142.50,,,,

,E,0730,1730,142.50,,,,

,时间计算五,,,,,,,

,,,,,,,,,

,用时间计算天数,,,,,,,

,姓名,周日加班,平时加班,计算实际加班时数,合计实际天数,,,

,A,8:00,21:00,29:00,3.63,.=E62*24/8,,

,A,8:00,21:00,29:00,3.63,,,

,B,16:00,22:00,38:00,4.75,,,

,C,16:00,23:00,39:00,4.88,,,

,D,20:00,22:30,42:30,5.31,,,

,E,12:00,20:30,32:30,4.06,,,

,,,,,,,,,

,,,,,,,,,

,时间计算六,,,,,,,

,,,,,,,,,

,无规律性的时间计算,,,,,,,

,姓名,周日加班,平时加班,转换时间格式(周日),转换时间格式(平时),合计,,

,A,080000,210000,8:00:00,21:00:00,29:00:00,".=TIME(LEFT(C76,2),MID(C76,3,2),RIGHT(C76,2))",

,A,100000,210001,10:00:00,21:00:01,31:00:01,".=TIME(LEFT(D76,2),MID(D76,3,2),RIGHT(D76,2))",

,B,160000,220000,16:00:00,22:00:00,38:00:00,,

,C,170000,230000,17:00:00,23:00:00,40:00:00,,

,D,200000,243000,20:00:00,0:30:00,20:30:00,,

,E,120000,203000,12:00:00,20:30:00,32:30:00,,

,时间计算七,,,,,,,

,,,,,,,,,

,计算相隔时间,,,,,,,

,姓名,开始时间,结束时间,相隔月份,相隔天数,相隔时间,,

,A,2005/1/18:00,2005/6/121:00,5月30日,151,3637.0,".=DAYS360(C92,E92)",

,A,2005/1/28:00,2005/3/221:00,2月28日,59,1429.0,.=(E92-C92)*24,

,B,2005/1/116:00,2005/2/522:00,2月4日,35,846.0,,

,C,2005/1/316:00,2005/4/323:00,3月30日,90,2167.0,,

,D,2005/2/120:00,2005/3/122:30,1月28日,28,674.5,,

,E,2005/3/112:00,2005/4/113:00,1月31日,31,745.0,,

,,,,,,,,,

,时间计算八,,,,,,,

,,,,,,,,,

,计算实际上班时数(减去休息时间),,,,,,,

,,,,,,,,,

,姓名,上班时间,下班时间,上班时间,下班时间,求D107-D112列实际时数,求E107-E112列实际时数,

,A,2005/7/77:30,2005/7/711:30,2005/7/713:00,2005/7/717:00,4.00,4.00,".=(F107-E107)*24-(DATEDIF(E107,F107,""d""))*20"

,A,2005/7/77:30,2005/7/811:30,2005/7/713:00,2005/7/817:00,8.00,8.00,".=(F108-E108)*24-(DATEDIF(E108,F108,""d""))*20"

,B,2005/7/77:30,2005/7/911:30,2005/7/713:00,2005/7/920:30,12.00,15.50,

,C,2005/7/77:30,2005/7/1011:30,2005/7/713:00,2005/7/1023:00,16.00,22.00,

,D,2005/7/77:30,2005/7/1111:30,2005/7/713:00,2005/7/1122:30,20.00,25.50,

,E,2005/7/77:30,2005/7/1211:30,2005/7/713:00,2005/7/1223:30,24.00,30.50,

,,,,,,,,,

,,,,,,,,,

,时间计算九(新),,,,,,,

,,,,,,,,,

,计算实际秒数(无日期),,,,,,,

,姓名,开始时间,结束时间,实际(秒数)时间,,,,

,A,23:15:00,0:15:00,3600.00,".=IF(C121>D121,(D121+1-C121)*60*60*24,(D121-C121)*60*60*24)",,,

,A,0:15:01,0:20:12,311.00,,,,

,B,0:21:45,0:45:31,1426.00,,,,

,C,1:05:00,4:05:16,10816.00,,,,

,D,4:02:01,4:07:00,299.00,,,,

,E,3:02:01,3:07:00,299.00,,,,

,时间计算十(新),,,,,,,

,,,,,,,,,

,"""分钟格式""转为""时间格式""",,,,,,,

,姓名,时间数,时间格式显示,公式一,,,,

,A,30.6分钟,0:30:36,".=TIME(,LEFT(C138,4),MOD(LEFT(C138,4),1)*60)",,,,

,A,40.1分钟,0:40:06,,,,,

,B,50.4分钟,0:50:24,,,,,

,C,35.3分钟,0:35:18,,,,,

,D,20.4分钟,0:20:24,,,,,

,E,22.3分钟,0:22:18,,,,,

,姓名,时间数,时间格式显示,公式二,,,,

,A,30.6分钟,00:30:36,".=TEXT(LEFT(C138,4)/1440,""hh:mm:ss"")",,,,

,A,40.1分钟,00:40:06,,,,,

,B,50.4分钟,00:50:24,,,,,

,C,35.3分钟,00:35:18,,,,,

,D,20.4分钟,00:20:24,,,,,

,E,22.3分钟,00:22:18,,,,,

,,,,,,,,,

,时间计算十一(新),,,,,,,

,,,,,,,,,

,提取日期/时间的部分值,,,,,,,

,日期,只需要年份,只需要月份,只需要日期,只需要时间,只需要分钟,,

,2005/7/912:10,2005,7,9,12,10,.=YEAR(B160),提取年份

,2005/8/1010:20,2005,8,10,10,20,.=MONTH(B160),提取月份

,2005/9/118:30,2005,9,11,8,30,.=DAY(B160),提取日期

,2005/10/1315:40,2005,10,13,15,40,.=HOUR(B160),提取分钟

,2005/11/143:05,2005,11,14,3,5,,

,2005/12/1614:02,2005,12,16,14,2,,

,日期,只需要秒数,显示星期,显第几周,,,,

,2005/7/912:10:59,59,7,28,.=SECOND(B170)提取秒数,,,

,2005/8/1010:20:03,3,4,33,.=WEEKDAY