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

1、今天是 2013-8-16 16:16Excel在內(nèi)部把每24小時(shí)的時(shí)間周期作為一個(gè)從0至1的小數(shù)存儲(chǔ)。所以設(shè)公式時(shí)就要注意了! 有一段限用（29次）的VBA如果你對(duì)VB/感興趣的可以研究一下代碼！時(shí)間計(jì)算計(jì)算實(shí)際加班時(shí)數(shù)姓名上班時(shí)間下班時(shí)間計(jì)算實(shí)際加班時(shí)數(shù)A18:0021:00:003:00B18:0022:00:004:00C18:0023:00:005:00D17:0022:30:005:30E18:0020:30:002:30時(shí)間計(jì)算計(jì)算加班費(fèi)，每小時(shí)加班費(fèi)17.85元廠姓名上班時(shí)間下班時(shí)間計(jì)算實(shí)際加班時(shí)數(shù) A18:0021:00:003:00B18:0022:00:004:00C18

2、:0023:00:005:00D17:0022:30:005:30E18:0020:30:002:30時(shí)間計(jì)算三計(jì)算加班費(fèi)，每小時(shí)加班費(fèi)17.85元匚姓名周日加班平時(shí)加班計(jì)算實(shí)際加班時(shí)數(shù) A8:0021:005:00A8:0021:0029:00B16:0022:0038:00C16:0023:0039:00D20:0022:3042:30E12:0020:3032:30計(jì)算文本格式的時(shí)間（一）姓名周日加班（文本）周日加班（轉(zhuǎn)為可計(jì)算）平時(shí)加班（文本）A034:0034094:30A034:0034094:30B034:0034097:30C034:0034084:00D034:0034095

3、:00E034:0034130:00新計(jì)算文本格式的時(shí)間（二），假設(shè)每小時(shí)15元計(jì)姓名周日加班平時(shí)加班當(dāng)日實(shí)得薪金A07301730142.50A07301830157.50B07302030187.50C07301730142.50D07301730142.50E07301730142.50時(shí)間計(jì)算五用時(shí)間計(jì)算天數(shù)廠姓名周日加班平時(shí)加班計(jì)算實(shí)際加班時(shí)數(shù)A8:0021:0029:00A8:0021:0029:00B16:0022:0038:00C16:0023:0039:00D20:0022:3042:30E12:0020:3032:30時(shí)間計(jì)算六無(wú)規(guī)律性的時(shí)間計(jì)算匚姓名周日加班平時(shí)加班轉(zhuǎn)換時(shí)

4、間格式（周日）匚 A0800002100008:00:00A10000021000110:00:00B16000022000016:00:00C17000023000017:00:00D20000024300020:00:00E12000020300012:00:00時(shí)間計(jì)算七計(jì)算相隔時(shí)間姓名開(kāi)始時(shí)間結(jié)束時(shí)間相隔月份A2005-1-1 8:002005-6-1 21:005 A2005-1-2 8:002005-3-2 21:002B2005-1-1 16:002005-2-5 22:002C2005-1-3 16:002005-4-3 23:003D2005-2-1 20:002005-3-

5、1 22:301E2005-3-1 12:002005-4-1 13:001時(shí)間計(jì)算八計(jì)算實(shí)際上班時(shí)數(shù)（減去休息時(shí)間）姓名上班時(shí)間下班時(shí)間上班時(shí)間|A2005-7-7 7:302005-7-7 11:302005-7-7 13:001 A2005-7-7 7:302005-7-8 11:302005-7-7 13:00B2005-7-7 7:302005-7-9 11:302005-7-7 13:00C2005-7-7 7:302005-7-10 11:302005-7-7 13:00D2005-7-7 7:302005-7-11 11:302005-7-7 13:00E2005-7-7 7:

6、302005-7-12 11:302005-7-7 13:00時(shí)間計(jì)算九（新）計(jì)算實(shí)際秒數(shù)（無(wú)日期）廠姓名開(kāi)始時(shí)間結(jié)束時(shí)間實(shí)際（秒數(shù)）時(shí)間A23:15:000:15:003600.00A0:15:010:20:12311.00B0:21:450:45:311426.00C1:05:004:05:1610816.00D4:02:014:07:00299.00E3:02:013:07:00299.00時(shí)間計(jì)算十（新）廠姓名時(shí)間數(shù)時(shí)間格式顯示A30.6分鐘00:30:36A40.1分鐘00:40:06B50.4分鐘00:50:24C35.3分鐘00:35:18D20.4分鐘00:20:24E22.3

7、分鐘00:22:18公式二.二TEXT(LEFT(C138,4)/1440,"hh:mm:ss""分鐘格式"轉(zhuǎn)為"時(shí)間格式"廠姓名時(shí)間數(shù)r時(shí)間格式顯示公式一 A30.6分鐘0:30:36.=TIME( ,L EFT(C138,4),MOD(LEFT(C138,A40.1分鐘0:40:06B50.4分鐘0:50:24C35.3分鐘0:35:18D20.4分鐘0:20:24E22.3分鐘0:22:18時(shí)間計(jì)算十一（新）提取日期/時(shí)間的部分值匚日期只需要年份只需要月份只需要日期2005-7-9 12:102005792005-8-10 10

8、:2020058102005-9-11 8:3020059112005-10-13 15:40200510132005-11-14 3:05200511142005-12-16 14:0220051216日期只需要秒數(shù)顯示星期顯第幾周2005-7-9 12:10:5959七282005-8-10 10:20:033四332005-9-11 8:30:1515-一一382005-10-13 15:40:2222五422005-11-14 3:05:3131-472005-12-16 14:02:4545六516:16存儲(chǔ)。所以設(shè)公式時(shí)就要注意了!公式:=C5-B5,直接相減就可以了加班費(fèi)5:33

9、¥ 71.40¥ 89.25¥ 98.18¥ 44.63直接相乘計(jì)算結(jié)果不正確（5:33）=(SUM(HOUR(SUM(D24,-C24),MINUTE(SUM(D24,-C24)/60)*17.85加班費(fèi)¥ 517.65¥ 678.30¥ 696.15¥ 758.63¥ 580.13.=C37+D37,直接相加不設(shè)"自定義格式"是錯(cuò)誤的.=C37+D37,直接相加（自定義格式h:mm,就正確了 .=（C39+D39）*24*17.85,時(shí)間相加后乘以24,相加班費(fèi).平時(shí)加班數(shù)（轉(zhuǎn)為可

10、計(jì)算）合計(jì).二VALUE(LEFT(C52,3)+VALUE(RIGHT(C52,2)/60單元格格式設(shè)為常規(guī)即可94.5128.594.5128.597.5131.58411895129130164合計(jì)實(shí)際天數(shù)3.63.二E62*24/83.634.754.885.31轉(zhuǎn)換時(shí)間格式（平時(shí)）合計(jì)21:00:0029:00:0021:00:0131:00:014.06=TIME(LEFT(C76,2),MID(C76,3,2),RIGHT(C76,2).二TIME(LEFT(D76,2),MID(D76,3,2),RIGHT(D76,2)22:00:0038:00:0023:00:0040:00

11、:000:30:0020:30:0020:30:0032:30:00相隔天數(shù)相隔時(shí)間1513637.0591429.035846.0902167.028674.531745.0.二DAYS360(C92,E92).=(E92-C92)*24下班時(shí)間求D107-D11別實(shí)際時(shí)姝E107-E112列實(shí)際時(shí)數(shù)12005-7-7 17:004.004.002005-7-8 17:008.008.002005-7-9 20:3012.0015.502005-7-10 23:0016.0022.002005-7-11 22:3020.0025.502005-7-12 23:3024.0030.50.=(F107-E107)*24-(DATEDIF(E107,F107,"d")*20.=(F108-E108)*24-(DATEDIF(E108,F108,"d")*20丄 EFT(C138,4),MOD(LEFT(C138,4),1)*60)LEFT(C138,4)/1440,"h