[[20201214154150]]　『時間の差分を求める（ロジックについて）』（WK）　ページの最後に飛ぶ

『時間の差分を求める（ロジックについて）』（WK）

メール受信時刻とメール返信時間の差分を求める際に、
下記要件を組み込めず悩んでおります。

【時間外・昼休憩中は差分計算時に加味しない。】

営業時間
10:00~12:00　※1時間の昼休憩
13:00~19:00

ex)
■時間外
12/14 5:00受信　/　12/14 11:00返信の場合
　　A.5:00を10:00に繰り上げて計算し、1時間が回答となる。

■お昼
12/14 5:00受信　/　12/14 17:00返信の場合
　　A.5:00を10:00に繰り上げて計算し、お昼時間を減算し、
　　　4時間が回答となる。

■お昼2
12/14 5:00受信　/　12/15 17:00返信の場合
　　A.5:00を10:00に繰り上げて計算し、お昼時間を2回減算し、
　　　12時間が回答となる。

→昼休憩を減算する場合としない場合が返信時間により異なる為、
　どのように作業したら良いのか思い付かず。
　※返信時間によっては前日のお昼休憩のみを減算する場合と、
　　当日も減算する必要がある場合との2種類があり。

日を跨ぐ場合も同様で、
19:00~の受信は次の日の10:00に受信したとみなします。

管理はExcelでメール受信・メール返信のタイムスタンプを取得しております。
作業列を作り、上記タイムスタンプの時間を時間外の場合は任意に変更したり、
昼休憩は計算時に減算する仕様としたいのですが、ロジックが思い付かずご助力頂きたく。

何卒よろしくお願い致します。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 複数日間にわたるケースもあるようなんですが、
 中間にある土日祝はどんな扱いになるんですか？

（半平太） 2020/12/14(月) 16:16

半平太ご返信ありがとう御座います。
シフト制業務となりますが、休日はそのまま加算されてしまって平気です。

ex)
N日10:00受信・N日+1公休・N+2返信14:00の場合、
19時間経過として計算
（WK） 2020/12/14(月) 16:20

半平太さん ※先程の返信は敬称を付け忘れてしまい申し訳御座いませんでした。
（WK） 2020/12/14(月) 16:21

 なんかややこしいですね。

 １日を0時～9時しかないものと考えます(正味8時間）。
 すると休憩は2時～3時の間の１時間となります。

 そう考えると、経過時間の計算のスタート(ST)は、受信日の午前0時となります。

 (ST）から修正受信時刻までの経過時間と、（ST）から修正返信時刻までの経過時間をそれぞれ算出し、
 両者の差を出せば、受信から返信までの経過時間と言うことになります。

 修正受信時刻は、「見做し受信時刻(一番早くて10時）」から10:00を差し引く
 修正返信時刻は、単に10:00を差し引く

 正味の経過時間は、休憩時間を差し引き後なので、受信、返信、それぞれ休憩時間を算出して減じることになります。

 (1) D2セル =IF("19:00"*1<MOD(B2,1)+"0:00:01",INT(B2)+1,MAX(INT(B2)+"10:00",B2)-"10:00")
 (2) E2セル =C2-"10:00"
 (3) F2セル =MAX(0,MIN("3:00",MOD(D2,1))-"2:00")
 (4) G2セル =MAX(0,MIN("3:00",MOD(E2,1))-"2:00")
 (5) H2セル =MOD(D2,1)-F2
 (6) I2セル =MOD(E2,1)-G2+(INT(E2)-INT(D2))*"8:00"
 (7) J2セル =I2-H2

 <結果図>
  行 __A__ ________B________ ________C________ _______D_______ _______E_______ _____F_____ _____G_____ ____H____ ____I____ __J__
   1 氏名  受信              返信              修正受信時刻    修正返信時刻    受信側休憩  返信側休憩  受信経過  返信経過  差異 
   2       2020/12/14 5:00   2020/12/14 11:00  2020/12/14 0:00 2020/12/14 1:00     0:00        0:00      0:00      1:00     1:00 
   3       2020/12/14 5:00   2020/12/14 17:00  2020/12/14 0:00 2020/12/14 7:00     0:00        1:00      0:00      6:00     6:00 
   4       2020/12/14 5:00   2020/12/15 17:00  2020/12/14 0:00 2020/12/15 7:00     0:00        1:00      0:00     14:00    14:00
   5       2020/12/14 14:00  2020/12/15 11:00  2020/12/14 4:00 2020/12/15 1:00     1:00        0:00      3:00      9:00     6:00 
   6       2020/12/14 11:00  2020/12/15 14:00  2020/12/14 1:00 2020/12/15 4:00     0:00        1:00      1:00     11:00    10:00
   7       2020/12/14 12:25  2020/12/15 14:00  2020/12/14 2:25 2020/12/15 4:00     0:25        1:00      2:00     11:00     9:00

 （半平太） 2020/12/14(月) 19:43

 追記

 ＞日を跨ぐ場合も同様で、
 ＞19:00~の受信は次の日の10:00に受信したとみなします。

 そこを見落としたので、上記(1)の数式を修正しておきました。　m(__)m

コメント返信：

[ 一覧(最新更新順) ]

[[20201214154150]] 『時間の差分を求める（ロジックについて）』（WK） ページの最後に飛ぶ

[[20201214154150]]　『時間の差分を求める（ロジックについて）』（WK）　ページの最後に飛ぶ