[已解决]如何计算两个日期之间的时间差并减去休息的时间~~

hcm19522

秒捨去 算到分=SUM(FREQUENCY(ROW(INDIRECT(INT(MOD(A2,1)*1440)&":"&INT((MOD(B2,1)+(MOD(B2,1)<MOD(A2,1)))*1440))),{54,78,84,108,198}*10)*{0;1;0;1;0;1})-1

hcm19522

cutecpu 发表于 2021-9-6 15:44
=TEXT(MAX(MIN(INT(A2)+13/24,1*B2)-A2,)+MAX(B2-INT(A2)-14/24,)-MAX(MIN(1*B2,INT(A2)+1.375)-INT(A2)-0. ...

刪-1   應後+1較 OK     ROW(INDIRECT(INT(MOD(A2,1)*1440)+1&":"

cutecpu

hcm19522 发表于 2021-9-7 17:00
刪-1   應後+1較 OK     ROW(INDIRECT(INT(MOD(A2,1)*1440)+1&":"

=SUM(FREQUENCY(ROW(INDIRECT(INT(MOD(A2,1)*1440+1%)+1&":"&INT((MOD(B2,1)+(INT(B2)>INT(A2)))*1440+1%))),{54,78,84,108,198}*10)*{0;1;0;1;0;1})

紅色: 修正要加 1 天的判斷方式。例如：2021/9/7 09:00 ~ 2021/9/8 09:00
藍色: 修正轉換分鐘時，浮點誤差。例如：原來 13:00  只能得到 779 分鐘！

Hjfvba66

cutecpu 发表于 2021-9-7 19:13
=SUM(FREQUENCY(ROW(INDIRECT(INT(MOD(A2,1)*1440+1%)+1&":"&INT((MOD(B2,1)+(INT(B2)>INT(A2)))*1440+1% ...

这个好像不对，比如

2020/6/2 9:17

2020/6/2 14:20

得出的应该是3个小时多一点，但是计算出来是243分钟，4个小时了

hcm19522

cutecpu 发表于 2021-9-7 19:13
=SUM(FREQUENCY(ROW(INDIRECT(INT(MOD(A2,1)*1440+1%)+1&":"&INT((MOD(B2,1)+(INT(B2)>INT(A2)))*1440+1% ...

INT(B2)>INT(A2) 比用MOD 正確

Hjfvba66

hugeinfo 发表于 2021-9-7 13:13
=IF(DATEDIF(A2,B2,"d")=0,IF(AND((A2-INT(A2))*24=14),(B2-INT(B2))*24-(A2-INT(A2))*24-2,),(B2-INT(B2 ...

您这个公式把那些没有跨越中午的数据给漏了

Hjfvba66

Hjfvba66 发表于 2021-9-8 09:28
这个好像不对，比如得出的应该是3个小时多一点，但是计算出来是243分钟，4个小时了

应该是78改成72，学到了，谢谢

AmoKat

C2
=SUM(--TEXT(MMULT(IF(--(MOD(A2:B2,1)+(MOD(A2,1)>MOD(A2:B2,1))>MID("09:00 13:00;14:00 18:00",{1;1;13;13}+{0,6},5)+{0;1;0;1})={1,0},MOD(A2:B2,1)+(MOD(A2,1)>MOD(A2:B2,1)),MID("09:00 13:00;14:00 18:00",{1;1;13;13}+{0,6},5)+{0;1;0;1}),{-1;1}),"[<]!0"))+INT(B2-A2)*8/24
三鍵輸入公式
下拉
2021/09/14 修正，公式重複貼2次

公式原理：每段上下班時間計算為 =  MAX(交接時間，下班時間) - MIN(接單時間，上班時間)

下式為 IF( 接單時間 > 上班時間，接單時間，上班時間) 及 IF( 交接時間 < 上班時間，交接時間，下班時間) 兩式合併IF(--(MOD(A2:B2,1)+(MOD(A2,1)>MOD(A2:B2,1))  >  MID("09:00 13:00;14:00 18:00",{1;1;13;13}+{0,6},5)+{0;1;0;1})={1,0},       MOD(A2:B2,1)+(MOD(A2,1)>MOD(A2:B2,1)),      MID("09:00 13:00;14:00 18:00",{1;1;13;13}+{0,6},5)+{0;1;0;1})

接單日期、交接日期取時間部分計算 1天內的工作時間，若 交接時間 小於  接單時間時，交接時間 + 1日(24小時)
+(MOD(A2,1)>MOD(A2:B2,1)) 此判斷式為 +{0,0} 或 + {0,1}，當交接時間 小於  接單時間 時為 +{0,1}

MID("09:00 13:00;14:00 18:00",{1;1;13;13}+{0,6},5)+{0;1;0;1} 將上班時間擴增為 今天及明天上班時間
{"09:00","13:00"}+0
{"09:00","13:00"}+1
{"14:00","18:00"}+0
{"14:00","18:00"}+1  

AmoKat

接單日期：2020-06-02 09:02:14 交接日期：2020-06-02 14:29:02
工時計算為 4:26:48，以取分鐘計時，不進位 266分鐘，進位 267分鐘
問題是進位是如何考慮的，無條件進或捨或以30秒上進或下捨

下L式偶而會增減1分鐘誤差，判斷是小數浮點誤差結果
L =SUM(FREQUENCY(MOD(ROW(INDIRECT("1:"&TEXT(B2-A2,"[m]")))/1440+A2,1),{9,13,14,18}/24)*{0;1;0;1;0})

下M式將 "日分割" 改為 "分鐘分割"，以整數分割避免浮點誤差
M =SUM(FREQUENCY(MOD(ROW(INDIRECT("1:"&TEXT(B2-A2,"[m]")))+A2*1440,1440),{9,13,14,18}*60)*{0;1;0;1;0})

上M式計算時將秒捨去不計，可以加下式以30秒上進或下捨
ROUND(TEXT(B2-A2,"s")/60,0)

Hjfvba66

AmoKat 发表于 2021-9-9 15:52
C2
=SUM(--TEXT(MMULT(IF(--(MOD(A2:B2,1)+(MOD(A2,1)>MOD(A2:B2,1))>MID("09:00 13:00;14:00 18:00",{1;1 ...

有点复杂不太理解