少し前から簡単な給与計算をエクセルで作っております。
入力項目は
名前/出社時間/退社時間/一日の労働時間合計
A子 8:15 19:00 9.5(h)
B男 8:15 17:15 8.0(h)
現状問題計算式で困っているが
遅刻と早退です。
1,定時が8:15、17:15で中途半端。間に10:00-10:07 12:00-12:45 15:00-15:08 の休憩時間は時間計算より除外したい。
2,一日の内で遅刻と早退両方されると、遅刻何時間、早退何時間と振り分け計算がよくわからない。
3,出来れば分単位でなく、30分単位で分かりたい。下記30分刻み表
8:15-8:45-9:15-9:45-10:30-11:00-11:30-12:00-13:15-13:45-14:15-14:45-15:15-15:45-16:15-16:45-17:15
例
B男 8:40 14:50 5.0(h) という入力より
遅刻時間 0.5h 早退時間 2.5h という答えをエクセルで引き出したいのです。
どうぞご教授のほどお願いします。
No.1ベストアンサー
- 回答日時:
遅刻時間は次の式で
=IF(OR(B2="",B2<="8:15"*1),"",(IF(CEILING(B2,"0:30")-"0:15">=B2,CEILING(B2,"0:30")-"0:15",CEILING(B2,"0:30")+"0:15")-"8:15")*24)
早退時間は次の式で
=IF(OR(C2="",C2>="17:15"*1),"",("17:15"-IF(FLOOR(C2,"0:30")+"0:15">C2,FLOOR(C2,"0:30")-"0:15",FLOOR(C2,"0:30")+"0:15"))*24)
なお、セルの書式設定で表示形式は標準にします。
この回答への補足
色々試してみたのですが、
体調悪くて遅刻9:00 そのまま11:30にDOWNして帰った場合や
昼から(13:00)に来て16:00に早退した場合などがうまくでません。
関数を解読してトライしてみます。
No.6
- 回答日時:
No.3・5です!
今回は全く別案です。
↓の画像で上側がSheet2になります。
これは単純に 出社時刻・退社時刻 による休憩時間を表にしただけです。
Sheet1の労働時間の部分で使用するために作ってみました。
(退社時刻-出社時刻-表内の休憩時間)とするため・・・
Sheet1のD2セルに
=IF(COUNTBLANK(B2:C2),"",FLOOR(C2-B2-INDEX(Sheet2!$B$2:$H$8,MATCH(B2,Sheet2!$A$2:$A$8,-1),MATCH(C2,Sheet2!$B$1:$H$1,-1)),"0:30")*24)
E2セルに
=IF(COUNTBLANK(B2:C2),"",IF(B2>TIMEVALUE("8:15"),CEILING(B2-"8:15","0:30")*24,""))
F2セルに
=IF(COUNTBLANK(B2:C2),"",IF(C2<"17:15",CEILING("17:15"-C2,"0:30")*24,""))
という数式を入れ、D2~F2セルを範囲指定 → F2セルのフィルハンドルで下へコピー!
これで画像のような感じになります。
※ Sheet2の休憩時間は正確かどうか判りませんので、今一度確認してみてください。
※ 労働時間重視でやっていますので、遅刻・早退はイマイチ自信がありません。
お役に立てば良いのですが・・・m(_ _)m
この回答への補足
ありがとうございます。
別理由により、労働時間は手入力が必須であり、現状は時間から遅刻、早退を引っ張りたい感じです。
皆様の式を一つづつ式の意味を調べてなんとか
C3出勤時間
B22 10:15
B23 12:15
T3 別の関数式で遅刻かどうかの判定のみ
I3に
=IF(T3="遅",IF(AND(C3>$B$22,C3<$B$23),((CEILING(C3,"0:30")-"8:30")*24),((IF(CEILING(C3,"0:30")-"0:15">=C3,CEILING(C3,"0:30")-"0:15",CEILING(C3,"0:30")+"0:15")-"8:15")*24)),"")
J3に
=IF(C3>=$B$23,I3-1,I3)
で二つのセルを使うことでなんとか遅刻を想定範囲で切り替えれるようになりました。
なんとか一つのセルに出来ればと思うのですが、長すぎて()が足りないのかすぐエラーが出ます。
もう一歩のところで詰まってしまってます。
No.5
- 回答日時:
No.3です!
たびたびごめんなさい。
前回の投稿で間違いがありました。
Sheet1のH2セル(休憩時間合計)の数式を
=IF(COUNTBLANK(B2:C2),"",VLOOKUP(C2,Sheet2!$D$2:$E$5,2,1)-VLOOKUP(B2,Sheet2!$C$2:$E$5,3,1))
に訂正してください。
前回の数式では
仮に12:00退社の人の場合、昼休憩を取っていないにも関わらず
午前中の休憩7分と昼休憩45分を合計した52分を引く計算になっていました。
何度も失礼しました。m(_ _)m
みなさん本当にありがとうございます。
即日の内にこんなに回答がいただけるなんて感激です。
毎日のシートがあり、そこから個々のシートに引っ張って集計しております。
定時時間や休憩時間は個々のシート内に記載しておりましたので、
そこに累計休憩時間を足させて頂きアレンジして頑張ってみます。
午前9:00出社 11:00退社
午後13:00出社 16:00退社
など、労働時間は問題無いのですが、遅刻時間、相対時間が昼休憩を挟むと狂ってしまうのですが何かいい方法ありますでしょうか?
上記のような場合はレアケースですが、体調不良で遅刻出勤して午前中にダウンとかいうのも実例でありましたので、そのような場合にも対応できれば素敵だなぁと。
我侭で申し訳ありません。
よろしければご教授の程宜しくお願い致します。
No.4
- 回答日時:
休憩時間内に出社/退社した人はどうする?
12:00 に出社/退社した人の遅刻/早退時間は、それぞれ 4.0(h)/5.5(h)
12:45 に出社/退社した人の遅刻/早退時間は、それぞれ 4.5(h)/4.5(h)
でOKですか?
上記の遅刻/早退時間には、別の休憩時間が含まれているがそれでOKですか?
この回答への補足
12:00に出勤しても、12:45に出勤しても休憩中ですのでカウントはしません。
労働時間は12:45スタートの計算になります。
お返事が遅くなりすみません。
基本休憩時間は計算から抜きたいです。
No.3
- 回答日時:
こんにちは!
一例です。
出社時刻が休憩時間以降・退社時刻が休憩時間前の場合も
考慮しなくてはならないと思いますので・・・
案として↓の画像のようにSheet2に表を作成しておきます。
Sheet2のE列は労働時間から休憩時間を引くために設けています。
Sheet1のH列に出社・退社時刻によって休憩時間をどれれか引けば良いか?を表示させておきます。
H2セルに
=IF(COUNTBLANK(B2:C2),"",VLOOKUP(C2,Sheet2!$C$2:$E$5,3,1)-VLOOKUP(B2,Sheet2!$C$2:$E$5,3,1))
という数式を入れオートフィルでずぃ~~~!っと下へコピー!
D2セルに
=IF(COUNTBLANK(B2:C2),"",FLOOR(C2-B2-H2,"0:30")*24)
E2セルに
=IF(COUNTBLANK(B2:C2),"",IF(B2>Sheet2!$A$2,CEILING(B2-Sheet2!$A$2,"0:30")*24,""))
F2セルに
=IF(COUNTBLANK(B2:C2),"",IF(C2<Sheet2!$B$2,CEILING(Sheet2!$B$2-C2,"0:30")*24,""))
(D2~F2セルの表示形式は「標準」で!)
という数式を入れ、D2~F2セルを犯意指定 → F2セルのフィルハンドルで下へコピー!
これで画像のような感じになります。
※ 一発でご希望の方法とはいかないと思いますが、
参考になりますかね?m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) エクセルの早退時間を計算したいです。計算はうまくいっているようですが… 1 2022/12/26 16:22
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
- バス・高速バス・夜行バス バスの待ち時間についてです。 私の地元のバス停は1時間に1本しか来ません。 なので例えば13時に目的 4 2022/06/25 12:14
- 就職 ホワイト企業ですか? 3 2023/03/10 15:16
- 就職 a型作業所に通います 週5日1日4.5時間ですが体調不良の場合は欠勤、早退、遅刻など可能なんでしょう 3 2022/05/23 15:51
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- 労働相談 有給の計算について 3 2023/05/20 13:11
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
アクセスクエリの計算
-
実行時エラー3131 FROM 句の構...
-
エクセルのデータをアクセスに...
-
Microsoft Accessをクレジット...
-
Access Error3061 パラメータが...
-
【至急・画像あり】建物or住所...
-
日付のテキストボックスに(例...
-
Access VBA を利用して、フォル...
-
Accessで作ったデータベースをw...
-
アクセスクエリで教えて下さい...
-
Access VBA [リモートサーバー...
-
Accessで独自メニューバーまた...
-
アクセス レポートを開いたとき...
-
Accessのクエリで、replace関数...
-
アクセスの更新クエリでカレン...
-
accessの代わりになるもの
-
テーブルとクエリの相関図は表...
-
ms access 2013で、チェックボ...
-
アクセスで教えてください。 ク...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
Access Error3061 パラメータが...
-
Microsoft365にAccessってあり...
-
Accessのクエリで、replace関数...
-
Accessのリンクテーブルのパス...
-
Access VBA [リモートサーバー...
-
ACCESS VBA でのエラー解決の根...
-
accessデータを指定したExcel、...
-
Accessのスプレッドシートエク...
-
CSVファイルの「0落ち」にVBA
-
【Access】Dcount関数の複数条...
-
Accessのフォーム上のテキスト...
-
Access VBA を利用して、フォル...
-
実行時エラー3131 FROM 句の構...
-
Vba Userformを前面に出すについて
-
Accessでフォームに自動入力し...
-
Accessレポートのチェックボッ...
-
Accessのテキストボックスの入...
-
Access 複数条件検索の設定が上...
-
accessのフォームに設置したボ...
おすすめ情報