Re: [算表] excel跨日計算案件完成時間

看板Office作者windknife18 (windknife18)時間14年前 (2011/08/18 02:01)推噓3(3推 0噓 4→)

留言7則, 3人參與討論串3/3 (看更多)

蠻好玩的問題, 將 text 函數拿來瘋狂使用, 應該可以更簡易, 不過沒力囉 E2 =IF(TEXT(TEXT(C2,"yyyy/m/d")-TEXT(B2,"yyyy/m/d"),"d")="0", (MIN(MAX(TEXT(TEXT(C2,"h:mm")-"08:30","[mm]"),0),3.5*60)+ MIN(MAX(TEXT(TEXT(C2,"h:mm")-"14:00","[mm]"),0),3.5*60))/60- (MIN(MAX(TEXT(TEXT(B2,"h:mm")-"08:30","[mm]"),0),3.5*60)+ MIN(MAX(TEXT(TEXT(B2,"h:mm")-"14:00","[mm]"),0),3.5*60))/60, (MIN(MAX(TEXT("12:00" - TEXT(B2,"h:mm"),"[mm]"),0),3.5*60)+MIN(MAX(TEXT("17:30"- TEXT(B2,"h:mm"),"[mm]"),0),3.5*60))/60+(TEXT(TEXT(C2,"yyyy/m/d")- TEXT(B2,"yyyy/m/d"),"d")-1)*7 +(MIN(MAX(TEXT(TEXT(C2,"h:mm")-"08:30","[mm]"),0),3.5*60)+ MIN(MAX(TEXT(TEXT(C2,"h:mm")-"14:00","[mm]"),0),3.5*60))/60) 參考範例 http://file.wikidot.com/local--files/f/20110818.xls ※ 引述《jiun0313 (jiun0313)》之銘言： : 不好意思各位@@ 我這邊再列的詳細一點! : 公司上班時間是8:30 下班時間是17:30 中午休息時間是12:00~14:00 : 所以上班工時7小時是 : 8:30~12:00=3.5小時 : 14:00~17:30=3.5小時 : 3.5+3.5=7 這樣來的 : 因此休息時間(也就是E欄標題)就是24-7=17 : 也就是說已經包含中午休息時間在內了 : 不知道這樣的想法是不是過於天真或草率...XD : 至於C案...那是我自己亂想的時候隨便列的XD : 我把它改成比較好算的時間了 : 感謝各位高手拔刀相助... : ※ 引述《jiun0313 (jiun0313)》之銘言： : : 軟體:excel 2007 : : 各位大大你們好，小的在這想問一個跟跨日計算工作時間相關的問題， : : 還請各位先進不吝賜教: : : A欄 B欄 C欄 D欄 E欄 : : 1(列) 開始結束結束-開始結束-開始-休息時間 : : 2 A案 2011/3/11 9:00 2011/3/14 10:00 73.00 22.00 : : 3 B案 2011/3/14 9:00 2011/3/17 10:00 73.00 22.00 : : 4 C案 2011/3/15 10:00 2011/3/17 10:50 48.83 14.83 : : 5 D案 2011/3/17 17:00 2011/3/18 10:00 17.00 17.00 : : 上面的時間是一個案子接收跟做好送出的時間， : : 分別以「開始」、「結束」來表示，小數點數值 : : 是以小時為單位。 : : 然後我要算一個案子從開始到結束花多久時間， : : 因此先直接相減，顯示在「結束-開始」那一欄， : : 但有些案子會花到兩天或三天以上的時間，於是 : : 我又再把一天中扣掉上班工時(7小時)之後的17小 : : 時扣掉，顯示在「結束-開始-休息時間」那一欄，公 : : 式如下: : : =IF(D2<=24,D2,IF(D2<=48,(D2-17),IF(D2<=72,(D2-34),IF(D2<=96,(D2-51),IF(D2<=120,( : : D2-68),IF(D2<=144,(D2-85),IF(D2<=168,(D2-102),IF(D2<=192,(D2-119),IF(D2<=216,(D : : 2-136),IF(D2<=240,(D2-153),IF(D2<=264,(D2-170),IF(D2<=288,(D2-187),D2)))))))))))) : : (請不要鞭太大力...) : : 我的想法是: : : 如果相減小於或等於24，就代表他是在一天以內完成案件，所以不用扣 : : 掉工時跟工時之間的間隔17小時(休息時間)，直接顯示D2就好， : : 但如果是在兩天以內完成，就扣除之， : : 但現在遇到一個灰熊困擾的問題，那就是在E5所顯示的時間(17.00) : : 雖然沒有超過24小時，但是他卻間隔了一天@@，若以上班時間8:30 : : 下班時間17:30計算的話，這個案子其實只做了2個小時，但因為直接 : : 相減已經小於24了，所以他就直接顯示17...要一個一個找出來再減掉 : : 實在太麻煩了(資料太多筆)，因此想問各位高手， : : 是不是我的想法錯誤了? : : 還是各位大大有更好的方法拯救@@? 還請各位大大不吝賜教...>< -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 122.116.146.144