パートさんの遅刻・早退・外出時間を自動計算する表を作っていますがうまく作れません・・・
下記の場合どうしたらよいでしょうか?
例えば遅刻の合計時間を算出するのに開始時刻から終了時刻を引き算しますが
10:20~10:30、 12:00~12:40、 14:50~15:00、は休憩時間なので自動的
に差し引きたいと考えています。
遅刻の時間内に、休憩時間である10:20~10:30が含まれれば10、
12:00~12:40が含まれれば40、14:50~15:00が含まれれば10を引くという感じです。
どのような計算式にすればよいのでしょうか?
ちなみに勤務時間は8:30~17:00です。
例えば、
(例)セル【B2】8:30(開始)セル【B3】13:00(終了)の場合、セル【B4】には50分を差引いた
3:40の合計時間を算出したいです。
色々調べて、B4に以下の式を入力してみましたが、どうもうまくいきません。
=IF(B3<>"",B3-B2-IF(TEXT(B3,"hh:mm")>"15:00","1:00",IF(TEXT(B3,"hh:mm")>"12:40","0:40",IF(TEXT(B3,"hh:mm")>"10:30","0:10","0:00"))))
どうぞ宜しくお願いいたします。
No.2ベストアンサー
- 回答日時:
こんばんは。
補足いただきありがとうございます。
認識といたしまして、B2およびB3には、遅刻、早退、外出問わず、
記入されているものと考えてよろしいですね。
式が長くなりますが以下のようにしてみるのはいかがでしょうか。
《訂正後》
=IF(B3<>"",B3-B2-IF(OR(TEXT(B2,"hh:mm")>="10:30",TEXT(B3,"hh:mm")<="10:20"),"0:00",IF(TEXT(B3,"hh:mm")>="10:30","10:30",TEXT(B3,"hh:mm"))-IF(TEXT(B2,"hh:mm")<="10:20","10:20",TEXT(B2,"hh:mm")))-IF(OR(TEXT(B2,"hh:mm")>="12:40",TEXT(B3,"hh:mm")<="12:00"),"0:00",IF(TEXT(B3,"hh:mm")>="12:40","12:40",TEXT(B3,"hh:mm"))-IF(TEXT(B2,"hh:mm")<="12:00","12:00",TEXT(B2,"hh:mm")))-IF(OR(TEXT(B2,"hh:mm")>="15:00",TEXT(B3,"hh:mm")<="14:50"),"0:00",IF(TEXT(B3,"hh:mm")>="15:00","15:00",TEXT(B3,"hh:mm"))-IF(TEXT(B2,"hh:mm")<="14:50","14:50",TEXT(B2,"hh:mm"))),"")
●検証
B2:"8:30"、B3:"10:00"の場合 ⇒ 1:30
B2:"8:30"、B3:"10:20"の場合 ⇒ 1:50
B2:"8:30"、B3:"10:25"の場合 ⇒ 1:50(5分が差し引かれている)
B2:"8:30"、B3:"10:30"の場合 ⇒ 1:50(10分が差し引かれている)
B2:"8:30"、B3:"12:00"の場合 ⇒ 3:20(10分が差し引かれている)
B2:"8:30"、B3:"12:40"の場合 ⇒ 3:20(50分が差し引かれている)
B2:"14:00"、B3:"15:00"の場合 ⇒ 0:50(10分が差し引かれている)
B2:"14:00"、B3:"17:00"の場合 ⇒ 2:50(10分が差し引かれている)
以上です。
●式について
以下のような構成になっています。
=IF(B3<>"", B3-B2- [後述の(1)] - [後述の(2)] - [後述の(3)], "")
(1)10:20~10:30の間の休憩時間(実時間)
(2)12:00~12:40の間の休憩時間(実時間)
(3)14:50~15:00の間の休憩時間(実時間)
いかがでしょうか。
早々の回答ありがとうございました。
おかげさまで、先ほど表が完成しました!
こんな長い式になるとは…
もともと入力していた式じゃだめなはずですね。
式の構成についても理解できました。
お礼が遅くなりましたが、本当に本当にありがとうございました!!
No.1
- 回答日時:
以下の点を修正するだけで要件を満たすことができると思います。
《修正前》
=IF(B3<>"",B3-B2-IF(TEXT(B3,"hh:mm")>"15:00","1:00",IF(TEXT(B3,"hh:mm")>"12:40","0:40",IF(TEXT(B3,"hh:mm")>"10:30","0:10","0:00"))))
《修正後》
=IF(B3<>"",B3-B2-IF(TEXT(B3,"hh:mm")>"15:00","1:00",IF(TEXT(B3,"hh:mm")>"12:40","0:50",IF(TEXT(B3,"hh:mm")>"10:30","0:10","0:00"))))
変更した箇所は1箇所だけです。
"0:40"を"0:50"に変更しました。
※以下、蛇足ですが、以下の点は考慮に入れる必要はありますか?
(1)B3に"12:20"が入力される。
⇒現行の式では10のみ引かれます。
(2)B3に"15:00"が入力される。
⇒現行の式では50のみ引かれます。
この要件を考慮に入れるともう少し複雑な式になります。
この回答への補足
早々の回答ありがとうございます!
そうなんです。
勤務時間8:30~17:00の間に発生した遅刻や早退、外出時間を
自動計算させたいのですが、
【遅刻】
○【12:00出社の場合】8:30~12:00=計3:20
○【12:40出社の場合】8:30~12:40=計3:20
共に勤務時間7:30のうち、3:20の遅刻となるはずが
下の段、12:40出社の場合、計が”4:00”となってしまうんです。
同様に外出した場合も、
【外出】
○【16:00から1時間外出した場合】16:00~17:00=計1:00
としたいのですが、現行の式ですと16:00~17:00=計0:00
となってしまうのです。
細々とすみません。どうぞ宜しくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- Excel(エクセル) Excel関数で日またぎの勤務時間にしるしを立てる 2 2022/04/20 17:22
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) エクセルの関数に関しての質問です。 5 2022/10/07 11:17
- Excel(エクセル) エクセルで勤務時間の計算をしています。 下記図でstartは勤務開始時間、endは勤務終了時間です。 5 2022/06/07 13:51
- Excel(エクセル) エクセルで休憩時間を引く時と、引かない時の数式 3 2022/11/05 11:48
- Excel(エクセル) 月間シフト表から総勤務時間を計算する関数 4 2023/05/20 07:12
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
Excelの時間計算で経過時間から特定の時間を引く方法
Excel(エクセル)
-
エクセルの早退時間を計算したいです。計算はうまくいっているようですが…
Excel(エクセル)
-
複数の休憩時間がある場合の休憩時間の算出方法
Excel(エクセル)
-
-
4
エクセルで勤怠管理表で休憩時間を計算させるには
Excel(エクセル)
-
5
遅刻・早退時間の関数
Excel(エクセル)
-
6
EXCELで勤務表を作成したいのですが、TIME関数を使用すると、残業時刻の所に遅刻や早退の時にエラ
Excel(エクセル)
-
7
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
8
エクセル不変則な休憩時間の実労働時間計算
Excel(エクセル)
-
9
遅刻,早退した時間の時,別のセルにも表示
Excel(エクセル)
-
10
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
-
11
Excel タイムカードで、昼休み時間を除く計算
Excel(エクセル)
-
12
excel関数 変動する休憩時間等を計算する
ソフトウェア
-
13
エクセルで時間の計算式を教えてください
Excel(エクセル)
-
14
IF関数について、14時以降のみに1時間の休憩を計算したい。
その他(ビジネス・キャリア)
-
15
エクセルで休憩時間を引く時と、引かない時の数式
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
DB2でのシステム日付のinsert、...
-
SQL*Loader フォーマット変換...
-
(日付 - 日付)/数値
-
SQL*Loaderでのsysdate使用
-
SQL Sever での日付の差の求め...
-
SQLエラーについて
-
ACCESS2000でクエリを抽出条件...
-
SQLです。下記の問合せを行うク...
-
Accessのマクロでモジュールを...
-
エクセルVBAでUserFormを起動し...
-
OutlookVBAで作成したマクロに...
-
今日の日付が入った行のデータ...
-
Access VBAで行ラベルが定義さ...
-
Texの枠囲み調節
-
PL/SQLカーソルの2重FORループ...
-
ODBCリンクの際にACCESSでは読...
-
Excelグラフの日付軸の日付がず...
-
キャッシュを使わずにSELECTを...
-
callで順に実行されるプロシー...
-
sqlplusでヘッダーが付かない
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SQL*Loaderでのsysdate使用
-
SQL*PLUSでの分秒までの表示の仕方
-
SQL*Loader フォーマット変換...
-
ALTER SESSIONについて
-
DB2でのシステム日付のinsert、...
-
日付型カラムへのデータINSERT
-
SQL Server で 翌月10日を取...
-
SQLです。下記の問合せを行うク...
-
to_date使用時に発生するエラー...
-
date型のインデックス
-
エクセル上の 2017/10/27 7:01...
-
EXCELでの時間計算(休憩時間の...
-
SQLエラーについて
-
SQL Sever での日付の差の求め...
-
(日付 - 日付)/数値
-
PL/SQLにて勤務時間の計算
-
date型へのinsertについて
-
実行エラー 3075:クエリ式`作...
-
yyy/mm/dd/形式
-
日付の範囲検索がしたい
おすすめ情報