少し長い質問になりますが、よろしくお願いいたします。
出社・退社の時間から、早朝残業や夕方残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。
残業となる時間の切り分けは、平日は以下のようになっています。、
早朝残業→5:00~8:30
基本時間→8:30~17:00
通常残業→17:00~22:00
深夜残業→22:00~翌朝5:00
例えば平日、朝6:00出社→23:00退社であれば、
早朝残業が2.5、通常残業が5.0、深夜残業が1.0となります。
深夜残業を出す数式を、以下のように作成してみたのですが、うまく算出できません。
C列に出社、D列に退社、入力はコロンを挟んだ時間の形式で、表示形式は[h]:mmです。
A列に日付、B列に祝日のフラグ、数式はG列に入れています。
=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))
ですが、出社13:00→退社27:00のところで、深夜残業が0.0になってしまい、
数式の IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00"))をOR式に変更しました。
=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(OR(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))
すると、出社0:00→退社18:30のところで、深夜残業が0.0になってしまいました。
両方をカバーできる数式はどのようになるのか、色々試しても出来ません。
お分かりになる方、いらっしゃいましたらお知恵を貸してください。
説明がうまく出来ていないかもしれません、分かりづらい文章で申し訳ありませんが
どうぞよろしくお願いいたします。
No.5ベストアンサー
- 回答日時:
回答No3です。
ごめんなさい。一部を修正してください。早朝残業のE2セルには次の式を入力して下方にドラッグコピーします。
=IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1,D2>"5:00"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,"")))
通常残業のF2セルには前回同様に次の式を入力して下方にドラッグコピーします。
=IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,"")))
深夜残業のG2セルには次の式を入力して下方にドラッグコピーします。
=IF(C2="","",IF(AND(C2<D2,C2>="5:00"*1),(MAX(D2,"22:00")-MAX(C2,"22:00"))*24, IF(AND(C2<D2,C2>=0),(MIN(D2,"5:00")-C2+MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2<="22:00"*1),(MIN(D2,"5:00")+"24:00"-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2>"22:00"*1),(MIN(D2,"5:00")+MAX(D2,"22:00")-"22:00")*24,"")))))
この回答への補足
昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。
数式の修正まで寄せてくださりありがとうございます!
早速、勤務時間を入力したところ、全ての時間で正しい深夜残業が算出出来ました!すごいですね・・・!
自分では頭がこんがらがってしまい、お手上げだったので本当に助かりました。
ありがとうございました!
No.7
- 回答日時:
>0:00~5:00の間に出勤した場合が、0になってしまいました。
それは質問文にない時間パターンですので、当然、正しく計算しません。質問者さんの責任の範疇ですので、ご自分で必要なセルを補ってください。
重複する時間帯の長さ、例えば 5:00?8:30 という時間帯と 6:00?23:00 という時間帯が重複している範囲は 6:00?8:30 ですが、その長さ 2:30 を求める数式は、max(,min-max) ということになっています。回答者の責任ではありませんので、ご自分で応用してください。
回答を寄せてくださり、ありがとうございます。
質問文の中に考えられるパターンを提示せず申し訳ありませんでした。
自分でも色々勉強しないといけませんね。
どうもありがとうございました!
No.4
- 回答日時:
C4 6:00
D4 23:00
F2 5:00
F3 8:30
G2 =f3
G3 17:00
H3 22:00
I3 29:00
I4 =max(,min($D4,I$3)-max($C4,I$2))
J4 =sum(f4:i4)
K4 =d4-c4=j4
を、添付図では入力しています。数式を全部入力したら、時間が表示されているセル範囲を選択した状態で、右クリックなどから「セルの書式設定>表示形式タブ>ユーザー定義>種類ボックス」に「[h]:mm」を入力。翌朝 1:00 を 25:00 などと表示します。
この回答への補足
昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。
回答を寄せて頂きありがとうございます!
画像まで付けてくださり、とてもわかりやすかったです。
ただ、サンプルの時間を入れてみましたら
0:00~5:00の間に出勤した場合が、0になってしまいました。
(2:15→15:15、0:00→10:30など)
ですが数式のヒントを頂けましたので自分でも、もっと試してみます!
どうもありがとうございました!
No.3
- 回答日時:
曜日などの表示は別としてC2セルから下方に出社時間、D2セルから下方に退社時間が入力されているとしてE列に早朝残業を表示させるのでしたら次の式をE2セルに入力して下方にドラッグコピーします。
=IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,"")))
F列に通常残業を表示させるのでしたら次の式をF2セルに入力して下方にドラッグコピーします。
=IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,"")))
G列に深夜残業を表示させるのでしたらF2セルに次の式を入力して下方にドラッグコピーします。
=IF(C2="","",IF(AND(C2<D2,C2>="5:00"*1,D2>="22:00"*1),(MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2<D2,C2>=0,D2>="22:00"*1),(MIN(D2,"5:00")-C2+MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2<D2,C2>=0,D2<"22:00"*1),MIN(D2,"5:00")*24,IF(AND(C2>D2,D2<="22:00"*1),(MIN(D2,"5:00")+"24:00"-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2>"22:00"*1),(MIN(D2,"5:00")+MAX(D2,"22:00")-"22:00")*24,""))))))
なお、時間の入力については0:00から23:59までの入力とします。また24時間以上の労働時間については考慮されていません。
この回答への補足
回答をありがとうございます!
深夜残業を出すにはこんなに長い条件が必要なのですね…勉強になります、ありがとうございます。
ただ、サンプルの時間を入れさせて頂きましたら、出社7:30→退社18:30でも深夜残業が5時間と出てしまいました。
回答して下さった式は私には難しく、中身を理解するには時間がかかってしまいそうなので、どこで5時間となるのかは不明なのですが…。
ですが、面倒な式を考えて下さり感謝いたします!
No.2
- 回答日時:
当日の05:00より前に(例えば03:00)出社して、翌朝(例えば04:00)まで仕事をするなんてことは考えなくてよろしいのですか?
つまり、「出社は当日の05:00以降、かつ、退社は翌日の05:00より前」ということ?
貴方の「説明がうまく出来ていないかもしれ」ないので、確認しています。
この回答への補足
早速ありがとうございます。
うまく説明できていなくてすみません。
確かに当日の05:00より前に出社して、翌朝4:00まで仕事をする、もあります・・・。
恥ずかしながら条件付けが全く出来ていないって事ですね・・・
No.1
- 回答日時:
考え方だけの説明になりますが、
・作業用として、H列・I列を用意
・退社が22:00以降ならば、その超過分を計算 → H列へ
・出社が5:00以前ならば、出社~5:00までを計算 → I列へ
↓
上記2つを足す。(G列)
ではダメなのでしょうか?
(どうしても作業用の列を使いたくない場合は、当然NGですけど)
この回答への補足
早速回答をありがとうございます。
作業列を使う方法ですね、確かにその方がきちんと整理されていいのかもしれません!
退社と出社と分けて数式を作ってみたいと思います。
ありがとうございました!
作業列を使い算出する方法を教えてくださりありがとうございました!
こんがらがっていた頭を整理しなくては、と気付きました。
皆様にお付き合い頂き、感謝しています。
ありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- Excel(エクセル) 勤務表ですが、思うように出来ません。 3 2023/05/19 06:43
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
- Excel(エクセル) WORKDAY関数 4 2023/06/08 13:23
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- 人事・法務・広報 みなし残業と深夜の割増賃金 1 2023/04/17 00:06
- Visual Basic(VBA) VBA Userformで一部別シートに転記がしたいのですが 2 2023/05/24 13:08
- Excel(エクセル) Excel関数で日またぎの勤務時間にしるしを立てる 2 2022/04/20 17:22
- 人事・法務・広報 みなし残業と手当の関係 1 2023/04/12 09:23
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Officeを開くたびの「再起動メ...
-
英数字のみ全角から半角に変換
-
マイクロソフト 一時使用コード...
-
マクロの書き方を教えて下さい
-
outlookのメールが固まってしま...
-
大学のレポート A4で1枚レポー...
-
エクセルでXLOOKUP関数...
-
会社PCのメールが更新されない
-
【Excel VBA】PDFを作成して,...
-
excelの画面のグリッド線の消滅。
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft Formsの「個人情報や...
-
PCを買い換えました。 今使って...
-
エクセルで英文字に入れた下線...
-
Office 2021 Professional Plus...
-
会社のTeamsのことで相談です。...
-
【Excel】セル内の文字が正しい...
-
みつも朗ってソフトはなにがで...
-
マクロ1があります。 A1のセル...
-
Outlook 電源OFFの受診の仕方
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
「生産性ソフトウェア」とは何...
-
会社PCのメールが更新されない
-
【関数】○年○ヶ月と表示された...
-
WEBの記事を印刷する際にA...
-
エクセルでXLOOKUP関数...
-
Microsoft familyに追加されま...
-
会社のOutlookにてメールを予約...
-
Microsoft Formsの「個人情報や...
-
Microsoft365の一部を解約したい
-
マクロ自動コピペ 貼り付ける場...
-
Outlook で宛先が複数の場合の人数
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
大学のレポート A4で1枚レポー...
-
office365って抵抗感ないですか?
-
Microsoftにofficeアプリについ...
-
Excel テーブル内の空白行の削除
-
マイクロソフト 一時使用コード...
おすすめ情報