少し長い質問になりますが、よろしくお願いいたします。
出社・退社の時間から、早朝残業や夕方残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。
残業となる時間の切り分けは、平日は以下のようになっています。、
早朝残業→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.1
- 回答日時:
考え方だけの説明になりますが、
・作業用として、H列・I列を用意
・退社が22:00以降ならば、その超過分を計算 → H列へ
・出社が5:00以前ならば、出社~5:00までを計算 → I列へ
↓
上記2つを足す。(G列)
ではダメなのでしょうか?
(どうしても作業用の列を使いたくない場合は、当然NGですけど)
この回答への補足
早速回答をありがとうございます。
作業列を使う方法ですね、確かにその方がきちんと整理されていいのかもしれません!
退社と出社と分けて数式を作ってみたいと思います。
ありがとうございました!
作業列を使い算出する方法を教えてくださりありがとうございました!
こんがらがっていた頭を整理しなくては、と気付きました。
皆様にお付き合い頂き、感謝しています。
ありがとうございました!
No.2
- 回答日時:
当日の05:00より前に(例えば03:00)出社して、翌朝(例えば04:00)まで仕事をするなんてことは考えなくてよろしいのですか?
つまり、「出社は当日の05:00以降、かつ、退社は翌日の05:00より前」ということ?
貴方の「説明がうまく出来ていないかもしれ」ないので、確認しています。
この回答への補足
早速ありがとうございます。
うまく説明できていなくてすみません。
確かに当日の05:00より前に出社して、翌朝4:00まで仕事をする、もあります・・・。
恥ずかしながら条件付けが全く出来ていないって事ですね・・・
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.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.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) ということになっています。回答者の責任ではありませんので、ご自分で応用してください。
回答を寄せてくださり、ありがとうございます。
質問文の中に考えられるパターンを提示せず申し訳ありませんでした。
自分でも色々勉強しないといけませんね。
どうもありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
高校生はアルバイトするべきだろうか?賛成だったら「賛」、否定だったら「否」を文頭においてあなたの意見を教えてください。
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセル2010 深夜残業時間の計算
Excel(エクセル)
-
エクセルで深夜勤務の時間計算
Excel(エクセル)
-
エクセル 深夜休憩時間(深夜労働時間)を求めたい
Excel(エクセル)
-
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Microsoft365で提供しているアプリ
-
会社PCのメールが更新されない
-
Microsoft Formsの「個人情報や...
-
Outlook で宛先が複数の場合の人数
-
Windows 11で、IME言語バー(IM...
-
大学のレポート A4で1枚レポー...
-
Excel テーブル内の空白行の削除
-
Officeを開くたびの「再起動メ...
-
エクセルでXLOOKUP関数...
-
teams設定教えて下さい。 ①ビデ...
-
マクロ1があります。 A1のセル...
-
【Excel VBA】PDFを作成して,...
-
会社のPCに入っているExcelでバ...
-
【マクロ】違うブックのCallス...
-
エクセルやワードを無料で使え...
-
outlookのメールが固まってしま...
-
英数字のみ全角から半角に変換
-
【マクロ】文字を1文字づつ、...
-
Microsoft365で写真をアルバム...
-
ステータスバーの合計に表示さ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
英数字のみ全角から半角に変換
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
大学のレポート A4で1枚レポー...
-
Officeを開くたびの「再起動メ...
-
Microsoft Formsの「個人情報や...
-
one drive使えるpcを買う
-
マクロ自動コピペ 貼り付ける場...
-
PDFのハイパーリンクを自動的に変更し...
-
あらかじめ用意したテンプレー...
-
別シートの年間行事表をカレン...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
office365って抵抗感ないですか?
-
ステータスバーの合計に表示さ...
-
【スプレドシート】IF関数の複...
-
Teamsで課題を提出します。 画...
-
Outlook で宛先が複数の場合の人数
-
Microsoft Officeを2台目のPCに...
おすすめ情報