エクセルで一日働いた労働時間の2つのデーターの重なった時間を計算できるようにしたいのですが、こまっています。
例)A1のセルにAさんの出勤時間である「8:00」を入力。
A2にAさんの退社時間である「12:00」を入力。
B1にAさんの二度目の出勤である「18;00」
B2にAさんの二度目の退社である「20:00」
少しスペースをあけて、A5にBさんの出勤時間である「10:00」
A6にBさんの退社時間である「19:00」
と入力をした場合、二人が会社にでている重なっている時間である「3時間」という答えを出すようにしたいのですが、何かいい方法はあるでしょうか?
よろしくおねがいします。
No.1ベストアンサー
- 回答日時:
時刻はすべて、文字列ではなくシリアル値(日付や時刻を表す数値)で入力されているとして、
=MAX(MIN(A2,A6)-MAX(A1,A5),0)+MAX(MIN(B2,A6)-MAX(B1,A5),0)
まず、Aの一度目の勤務時間と、Bの勤務時間について、重なっている時間は、
【A,Bのいずれもが出勤した時刻 = A,Bのうち、より遅く出勤した方の出勤時刻)】から、
【A,Bのいずれかが退社した時刻 = A,Bのうち、より早く退社した方の退社時刻)】まで。
・時刻Pと時刻Qのうち、より遅い方の時刻 ⇒ MAX(P,Q)
・時刻Pと時刻Qのうち、より早い方の時刻 ⇒ MIN(P,Q)
・時刻P~時刻Qまでの時間 ⇒ P-Q
ですから、
MIN(A2,A6)-MAX(A1,A5)
となります。
ただし、重なっている時間がない場合(例えば、Aの出勤前にBが退社した場合)
負の値が返ってしまいますので、この場合は0にする必要があります。
・時間Tが負の値ならば0とする計算 ⇒ MAX(T,0)
ですから、
MAX(MIN(A2,A6)-MAX(A1,A5),0)
となります。
Aの二度目の勤務時間についても同様に処理して加算します。
以上ご参考まで。
この回答への補足
回答ありがとうございます。うまくいきました♪
ただ、Aさんが出勤し、Bさんが休みの場合、この式では
うまくいきませんでした。
片方だけが出勤してる時に重なる時間が0時間にするには
どのようにすればいいでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの早退時間を計算したいです。計算はうまくいっているようですが… 1 2022/12/26 16:22
- Excel(エクセル) Excel関数で日またぎの勤務時間にしるしを立てる 2 2022/04/20 17:22
- Excel(エクセル) エクセルで休憩時間を引く時と、引かない時の数式 3 2022/11/05 11:48
- アルバイト・パート 22年10月から社会保険適用拡大について、詳しい方いらっしゃいましたら教えてください 2 2022/09/01 20:40
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- 労働相談 有給の計算について 3 2023/05/20 13:11
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- 人事・法務・広報 みなし残業と手当の関係 1 2023/04/12 09:23
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
エクセル 時間帯の重複の有無
その他(Microsoft Office)
-
複数時間情報の重複を求める
Visual Basic(VBA)
-
時間帯の重複を除いた集計について
Excel(エクセル)
-
-
4
時間の重複チェック
Excel(エクセル)
-
5
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
6
2つの期間の重複する日数(月数)を計算するには?
Word(ワード)
-
7
エクセル 所定の期間に重なっている期間を表示するには?
Excel(エクセル)
-
8
機械の運転時間帯を表すグラフを作りたい。
Excel(エクセル)
-
9
エクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。
Excel(エクセル)
-
10
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
11
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
12
(エクセル関数)指定期間内で重複を除いて数え上げる方法
Excel(エクセル)
-
13
SUMPRODUCT 複数条件の重複データ件数
Excel(エクセル)
-
14
エクセル(EXCEL)で15分単位で加算したい場合
Excel(エクセル)
-
15
エクセルでの期間の重複について
Excel(エクセル)
-
16
excel関数 変動する休憩時間等を計算する
ソフトウェア
-
17
エクセル「社員の重なっている仕事時間の算出方法について」教えてください。
Excel(エクセル)
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
変形四角形の面積を計算するソフト
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルVBA、別ブックへ転記す...
-
【EXCEL】条件に一致した最新デ...
-
エクセルで二つの数字の小さい...
-
エクセルで時刻(8:00~20:00)...
-
PowerPointで表の1つの列だけ...
-
SUMIFとCOUNTIFを合わせたよう...
-
エクセル 文字数 多い順 並...
-
エクセルでオートフィルタのボ...
-
2つのエクセルのデータを同じよ...
-
エクセル(勝手に太字になる)
-
Excelのオートフィルタで非表示...
-
エクセル 同じ値を探して隣の...
-
エクセルでの複数条件下での標...
-
Excel関数で、範囲内の最後のセ...
-
エクセルの並び変えで、空白セ...
-
エクセルで、2種類のデータを...
-
エクセルのオートフィルタで最...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで数字を入力すると自...
-
エクセル 重なる時間の計算方法
-
JWCAD勾配入力方法
-
回転体の体積を求める方法
-
エクセルで残業時間を時間帯に...
-
勤怠管理で始業時刻を基準に計...
-
a1=1/5,an+1=an/4an-1 おしえて...
-
エクセルで1:00の90分後...
-
Excelでこんな関数ありませんか...
-
DRA-CADの計算機能につ...
-
変形四角形の面積を計算するソフト
-
(int)/(int)=(double)にする方法
-
Excelについて教えてください。...
-
エクセルで年月期間を4桁表示...
-
エクセルの枠の複数選択
-
mysqlで15分きざみで現在時刻を...
-
数量3.450 単価28.5 で40%を引...
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで二つの数字の小さい...
おすすめ情報