休息時間の計算をしていて以下のようなものを計算したいのですが、
その最終目標の計算式に到達することができません。
参考に画像を添付いたします。ご教授ください。
--------------------------------------------
表示したいのは「休息時間」の部分です。
1)前日の終業時刻、当日の始業時刻の両方が空欄の場合
→「休」と表示
2)前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合
→24時間+(当日0:00から当日始業時刻までの時間) の時間
3)当日の始業時刻が空欄で前日の終業時刻に値が入力されてる場合
→24時間+(前日終業時刻から当日0:00までの時間) の時間
4)前日の終業時刻、当日の始業時刻の両方とも値が入力されている場合
→(当日の始業時刻)-(前日の終業時刻)の時間
2)と3)をどうしたら良いかわからないので、
現在暫定的に2)か3)に該当する場合は「24時間以上」と表示するようにしており、
例えばセルE3には以下のような計算式を入れております。
=IF(AND($D2="",$C3=""),"休",IF(OR($D2="",$C3=""),"24時間以上",$C3-$D2))
以上 宜しく御願い申し上げます。
No.4ベストアンサー
- 回答日時:
>前日出勤が無いので3月3日はまるまる休み扱いとなり、
>23:59迄前日の休息時間とカウントされている為、
>ここは3月4日0:00から17:00迄の時間を表示させたい。
だとすると,また辻褄が合わなくなっています。
3/8についてですが,3/7が空白-空白で本来の意味で「休」になっており,同じように「(3/7の)23:59まで全日の休息時間とカウントされている」ため,3/8は同様に0時~始業時までのカウントで良くなります。
つまり元のご相談の状況提示で
「2)前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合
→24時間+(当日0:00から当日始業時刻までの時間) の時間」
の部分は全て,24時間を足さずに「0時~始業時刻まで」だけの計算で良いハズです。
その前提で添付図は
E3:
=IF(C3="",IF(D2<=A3-"8:0","休",A3-D2+1),C3-IF(D2="",A3,D2))
この回答への補足
お礼で補足内容をつけてしまいました。すみません。
補足入力ですべきでした。
ちなみに私がお礼部分に記載した式(keithinさんの式につけたした部分)は
終業時刻が翌日0:00を超えない場合、終業時刻から翌日0:00迄の時刻を加えたものにしたつもりです。
またまたご回答くださり、本当に感謝いたします。
そして、再度のご指摘ありがとうございます。
いただいたこの回答とご指摘いただいた内容を読み、もう一度考え直してみました。
そしてご指摘を踏まえたうえでkeithinさんのご回答を参考にしながら
以下のような式を当てはめたところ、恐らく正しく表示したかったはずの形になったように思います。
E3:
=IF(C3="",IF(D2<=A3-"8:0","休",A3-D2+1),C3-IF(D2="",A3,D2)+IF(C4="",IF(D3<A4,A4-D3)))
これで辻褄も合うようになったと思うのですが、どうでしょうか?
何度も続いて申し訳ないので、お時間がある時にご覧いただければ幸いです。
No.3
- 回答日時:
3/4の終業のように翌日にまたいでいる場合,3/5の「当日ゼロ時との差」は「-1時間」なので少なくとも「24時間以上」ではなくなります。
それに伴い「当日」を確定するため(特にC列にデータが無い場合),A列にきちんと年/月/日形式で日付が記入してあるものとします。
そういう前提でご質問に書かれているとおりに計算すると
E3:
=CHOOSE(COUNT(D2,C3)+1,"休",MOD(C3-D2,1)+(A3>D2),C3-D2)
のようになります。
E列はセルの書式設定の表示形式のユーザー定義で
[h]:mm
と設定します。
#1日休みを挟むと24時間がダブルカウントされるような気がしてなりませんが。
この回答への補足
回答ありがとうございます。
>1日休みを挟むと24時間がダブルカウントされるような気がしてなりませんが。
本当ですね!大変すみません。
そうなるとそもそも質問がおかしな状態だということに気づきました。
添付画像のエクセルに実際式を当て込むと実際大きなミスが自分にあったことがわかりました。
質問を補足・修正させてください・・・;
休息時間は24時間+8時間以上で「休」と見なされるのでそのように表示させたいのです。
ですが、そういう計算ができるかも、という事すら頭に閃かなかったので最初の質問では
時間をそのまま表示させたい、と申し上げておりました。
画像が添付できないので、見苦しいですがテキスト表示いたします。
A C D E
1 日付 始業時刻 終業時刻 休息時間
2 3月1日 2011/3/1 8:00 2011/3/1 17:00 15時間00分
3 3月2日 2011/3/2 6:00 2011/3/2 13:00 13時間00分
4 3月3日 35時間00分
5 3月4日 2011/3/4 17:00 2011/3/5 1:00 41時間00分
6 3月5日 23時間00分
7 3月6日 休
8 3月7日 休
9 3月8日 2011/3/8 8:00 2011/3/8 16:00 32時間00分
先ほどご回答くださった計算式を当てはめましたら
確かに、この場合ですとE5の時間がかぶってしまっておりおかしかったです。
■本来はE4のセルは24時間+8時間=34時間以上なのでE7、E8同様「休」と表示させたいです。
■E5は、私が申し上げてた質問では時間が被ってしまっておりますが、
前日出勤が無いので3月3日はまるまる休み扱いとなり、23:59迄前日の休息時間とカウントされている為、
ここは3月4日0:00から17:00迄の時間を表示させたい。
これが正しく表示させたい内容です。
せっかくお時間を割いてご回答くださったのに失礼いたしました。
No.1
- 回答日時:
24時間以上の表示の場合セルの書式設定で[h]のように括弧で囲います
(この括弧の名称は何だっけ?)
計算式は
2)前日の終業時刻が空欄で当日の始業時刻に値が入力されてる場合
→24時間+(当日0:00から当日始業時刻までの時間) の時間
の場合は
=$C3-INT($C3)+1
3)当日の始業時刻が空欄で前日の終業時刻に値が入力されてる場合
→24時間+(前日終業時刻から当日0:00までの時間) の時間
の場合は
=2-$D2-INT($D2)
※共にE3セルに計算式が有る場合
セルの表示形式を
[h]"時間"mm"分"
に設定
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- 建築学 ネットワーク工程表(1級管工事施工管理) 5 2022/08/24 08:17
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- 建設業・製造業 電力不足による、製造業の稼働時間はずらすことはできないでしょうか? 6 2022/06/27 18:31
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- 就職 a型作業所に通います 週5日1日4.5時間ですが体調不良の場合は欠勤、早退、遅刻など可能なんでしょう 3 2022/05/23 15:51
- Excel(エクセル) 出退勤管理の早退時間について 4 2023/08/21 15:22
このQ&Aを見た人はこんなQ&Aも見ています
-
新NISA制度は今までと何が変わる?非課税枠の拡大や投資対象の変更などを解説!
少額から投資を行う人のための非課税制度であるNISAが、2024年に改正される。おすすめの銘柄や投資額の目安について教えてもらった。
-
エクセルで休息時間計算したい
Excel(エクセル)
-
エクセルの質問です。 拘束時間の管理表作成しているのですが、当日の始業時間と翌日の始業時間に差がある
Excel(エクセル)
-
excel関数 変動する休憩時間等を計算する
ソフトウェア
-
-
4
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
5
エクセルで休憩時間を計算したいのですが 条件 勤務時間6時間以上8時間未満は休憩45分 8時間以上は
Excel(エクセル)
-
6
エクセルで勤怠管理表で休憩時間を計算させるには
Excel(エクセル)
-
7
エクセルで時間の判定をしています。0時以上5時以下の場合は○と判定したいので、以下のイフ関数を試しま
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
首吊りどこ締めるの
-
至急!尿検査前日にオナニーし...
-
腕を見たら黄色くなってる部分...
-
検便についてです。 便は取れた...
-
彼女のことが好きすぎて彼女の...
-
勃起する時って痛いんですか? ...
-
精子が黄色?
-
口の中に黒い血の塊
-
白血球が多いとどんな心配があ...
-
尿検査前日に自慰行為した時の...
-
中出しをするとお腹が痛い・・・。
-
精子に血が・・・
-
これって喉仏ですか? 私は女性...
-
爪が紫色?
-
納豆食べた後の尿の納豆臭は何故?
-
筋トレするとチンコが縮んじゃ...
-
射精をして1週間以内に尿検査を...
-
尿検査の前日は自慰控えたほう...
-
EXCELで式からグラフを描くには?
-
excelでsin二乗のやり方を教え...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
至急!尿検査前日にオナニーし...
-
首吊りどこ締めるの
-
尿検査の前日は自慰控えたほう...
-
尿検査前日に自慰行為した時の...
-
検便についてです。 便は取れた...
-
白血球が多いとどんな心配があ...
-
中出しをするとお腹が痛い・・・。
-
射精をして1週間以内に尿検査を...
-
彼女のことが好きすぎて彼女の...
-
腕を見たら黄色くなってる部分...
-
勃起する時って痛いんですか? ...
-
変な話しになります。尿検査で...
-
これって喉仏ですか? 私は女性...
-
EXCELで条件付き書式で空白セル...
-
男です。昨日の午後3時くらいに...
-
今朝、毎朝の習慣でオナニーし...
-
納豆食べた後の尿の納豆臭は何故?
-
1日前の検尿
-
値が入っているときだけ計算結...
-
精子が黄色?
おすすめ情報