EXCEL中時(shí)間計(jì)算公式

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

,Excel在內(nèi)部把每24小時(shí)的時(shí)間周期作為一個(gè)從0至1的小數(shù)存儲(chǔ)。所以設(shè)公式時(shí)就要注意了!,,,,,,,

,"有一段限用(29次)的VBA,如果你對(duì)VBA感興趣的可以研究一下代碼!",,,,,,,

,,,,,,,,,

,,,,,,,,,

,時(shí)間計(jì)算一,,,,,,,

,,,,,,,,,

,計(jì)算實(shí)際加班時(shí)數(shù),,,,,,,

,,,,,,,,,

,姓名,上班時(shí)間,下班時(shí)間,計(jì)算實(shí)際加班時(shí)數(shù),,,,

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

,,,,,,,,,

,,,,,,,,,

,時(shí)間計(jì)算二,,,,,,,

,,,,,,,,,

,"計(jì)算加班費(fèi),每小時(shí)加班費(fèi)17.85元",,,,,,,

,,,,,,,,,

,姓名,上班時(shí)間,下班時(shí)間,計(jì)算實(shí)際加班時(shí)數(shù),加班費(fèi),,,

,A,18:00,21:00:00,3:00,5:33,直接相乘計(jì)算結(jié)果不正確(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,,,

,時(shí)間計(jì)算三,,,,,,,

,,,,,,,,,

,"計(jì)算加班費(fèi),每小時(shí)加班費(fèi)17.85元",,,,,,,

,姓名,周日加班,平時(shí)加班,計(jì)算實(shí)際加班時(shí)數(shù),加班費(fèi),,,

,A,8:00,21:00,5:00,,".=C37+D37,直接相加不設(shè)""自定義格式""是錯(cuò)誤的.",,

,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,時(shí)間相加后乘以24,相加班費(fèi).",,

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

,時(shí)間計(jì)算四(新),,,,,,,

,,,,,,,,,

,計(jì)算文本格式的時(shí)間(一),,,,,,,

,姓名,周日加班(文本),周日加班(轉(zhuǎn)為可計(jì)算),平時(shí)加班(文本),平時(shí)加班數(shù)(轉(zhuǎn)為可計(jì)算),合計(jì),,

,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,單元格格式設(shè)為常規(guī)即可,

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

,新,,,,,,,

,"計(jì)算文本格式的時(shí)間(二),假設(shè)每小時(shí)15元計(jì)",,,,,,,

,,,,,,,,,

,姓名,周日加班,平時(shí)加班,當(dāng)日實(shí)得薪金,,,,

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

,時(shí)間計(jì)算五,,,,,,,

,,,,,,,,,

,用時(shí)間計(jì)算天數(shù),,,,,,,

,姓名,周日加班,平時(shí)加班,計(jì)算實(shí)際加班時(shí)數(shù),合計(jì)實(shí)際天數(shù),,,

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

,,,,,,,,,

,,,,,,,,,

,時(shí)間計(jì)算六,,,,,,,

,,,,,,,,,

,無(wú)規(guī)律性的時(shí)間計(jì)算,,,,,,,

,姓名,周日加班,平時(shí)加班,轉(zhuǎn)換時(shí)間格式(周日),轉(zhuǎn)換時(shí)間格式(平時(shí)),合計(jì),,

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

,時(shí)間計(jì)算七,,,,,,,

,,,,,,,,,

,計(jì)算相隔時(shí)間,,,,,,,

,姓名,開(kāi)始時(shí)間,結(jié)束時(shí)間,相隔月份,相隔天數(shù),相隔時(shí)間,,

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

,,,,,,,,,

,時(shí)間計(jì)算八,,,,,,,

,,,,,,,,,

,計(jì)算實(shí)際上班時(shí)數(shù)(減去休息時(shí)間),,,,,,,

,,,,,,,,,

,姓名,上班時(shí)間,下班時(shí)間,上班時(shí)間,下班時(shí)間,求D107-D112列實(shí)際時(shí)數(shù),求E107-E112列實(shí)際時(shí)數(shù),

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

,,,,,,,,,

,,,,,,,,,

,時(shí)間計(jì)算九(新),,,,,,,

,,,,,,,,,

,計(jì)算實(shí)際秒數(shù)(無(wú)日期),,,,,,,

,姓名,開(kāi)始時(shí)間,結(jié)束時(shí)間,實(shí)際(秒數(shù))時(shí)間,,,,

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

,時(shí)間計(jì)算十(新),,,,,,,

,,,,,,,,,

,"""分鐘格式""轉(zhuǎn)為""時(shí)間格式""",,,,,,,

,姓名,時(shí)間數(shù),時(shí)間格式顯示,公式一,,,,

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

,姓名,時(shí)間數(shù),時(shí)間格式顯示,公式二,,,,

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

,,,,,,,,,

,時(shí)間計(jì)算十一(新),,,,,,,

,,,,,,,,,

,提取日期/時(shí)間的部分值,,,,,,,

,日期,只需要年份,只需要月份,只需要日期,只需要時(shí)間,只需要分鐘,,

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

,日期,只需要秒數(shù),顯示星期,顯第幾周,,,,

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

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