エクセル2003を使用しています。
機械でタイムカードを打刻したあと、最終的に私がエクセルに入れて集計しています。
就業開始時間前は何もつかないので、出社時間を入れず
退社時間のみで表を作っています。
何点かできるといいなあと思う事がありまして。。色々調べているのですが、
ぴったりの回答が見つからなく困ってしまいました。
宜しくお願いします。
勤務時間8:30~17:00 所定 7.5時間 残業は10分単位です。
この単位は月末に最終的に丸めます。
例)17:15→15分残業 17:23→23分残業 ・・合計3時間13分 →3時間10分
また、17:10までに退社した場合は、その10分間はカウントしません。
今エクセルで17:00からの残業時間を終業時間-17:00で 0.23分などとだしています。
質問
1.17:10までに退社した場合は残業時間を表示しない
ifでやるのかな~と思って入れてみましたが、分の単位なのでうまくできません。。
2.家庭の都合で 途中中抜け(私用外出)をする人がいます。
その方は別に外出した時間をマイナスとして合計したいのです。
例)10:10~10:42 外出 マイナス32分(こちらも最終的に10分でまるめます)
今は時間を書き出し電卓で計算しています^^;
エクセル上でマイナスの時間だけの行を作って集計できたらいいなあ。。と。。
本当は出社・退社時間をきっちり入れてしっかりした勤務表がいいのでしょうが、
技術と時間がありません。。
まず上記のことだけできるといいなあと思っています。
何かと情報の記載漏れがあるかもしれませんが
宜しくおねがいします。
No.3ベストアンサー
- 回答日時:
例えばA1セルに出社時刻、B1セルに退社時刻、C1セルに勤務時間、D1セルに残業時間、E1セルに外出始時刻、F1セルに外出終時刻、G1セルに外出時間の文字がそれぞれ入力されており、A列、B列、E列、F列には下行にそれぞれ時刻が入力されるとします。
C2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",MIN(B2,"17:00")-MAX(A2,"8:30")-"1:00")
D2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",IF(B2>"17:10"*1,B2-"17:00",0))
G2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(COUNT(E2:F2)=0,"",F2-E2)
そして最後には集計した時間を10分単位で丸め込んだ時間にするわけですがそのデータを例えば26行目に表示させるとしたらC26セルには次の式を入力します。FLOOR関数は数値を切り捨てて基準値の倍数にします。
=FLOOR(SUM(C2:C25),"0:10")
D26セルには次の式を入力します。
=FLOOR(SUM(D2:D25),"0:10")
G26セルには次の式を入力します。CEILING関数は数値を切り上げて基準値の倍数にします。
=CEILING(SUM(G2:G25),"0:10")
なお、勤務時間から外出時間を引くのでしたらC26セルの式は
=FLOOR(SUM(C2:C25),"0:10")-G26
勤務時間の計算などではMIN関数、MAX関数、FLOOR関数、CEILING関数などが使われますのでよく理解しておくことがよいでしょう。
なお、お示しした表では出社時刻の列は時刻の入力が無くても、その場合には8:30からの開始として取り扱われます。しかし、実際には遅刻をして出社する場合もあるのではないですか。
関数を入力して計算結果が小数点などで表示される場合にはセルの書式設定から表示形式で時刻を選んでください。
すごいです!教えて頂いたように作成してみましたら、とても都合よく
表示され感動しております(TT)
ただ、式の内容を理解するには(自分で作成できるようになるには)
時間がかかりそうなので、先にお礼をさせて頂きます^^;
今回FLOOR関数は理解したところだったのですが、
他の関数も頑張って覚えようと思います。本当に助かりました。ありがとうございました!
No.2
- 回答日時:
No.1
- 回答日時:
質問1
=IF(A1>=TIME(17,10,0),A1-TIME(17,0,0),0)
A1は、退社時間
TIME(17,10,0) は、切り捨て時刻 時:分:秒で設定
ようするに 退社時間が17:10より大きいときに0とするということです
A1-TIME(17,0,0),
は、退社時刻マイナス17:00 ということです。
質問2
=戻ってくる時間-出ていく時間
としておいて、実際の計算のときに-A1のようにしては、マイナス符号をつけては、いかがでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- Excel(エクセル) 勤務表ですが、思うように出来ません。 3 2023/05/19 06:43
- 労働相談 有給休暇使用時の賃金の計算方法について 5 2022/04/04 00:02
- 会社・職場 夜勤中の眠気について 3 2022/09/02 07:49
- Excel(エクセル) 勤務表の中抜け集計の関数を教えてほしいです。 1 2023/01/16 01:26
- 労働相談 労働時間が五、六時間 その弐 2 2023/04/04 12:36
- 事務・総務 タイムカード打刻について 4 2023/05/24 07:55
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
A1セルに入力したら、入力時間...
-
参照先セルに値が入っていない...
-
Excelで同じセルに箇条書きをし...
-
IF関数で0より大きい数値が入力...
-
値を入力後、自動的にアクティ...
-
エクセルで既に入力してある文...
-
yyyy/mm/ddからyyyy/mmへの変換
-
エクセルで複数の条件で掛け算...
-
エクセルで時間の判定をしてい...
-
Excel:文字と数字の組合せ、次...
-
エクセルの数式で計算結果に文...
-
空白でないセルの値を返す方法...
-
Excelでセルに入力されたカラー...
-
エクセルで、特定のセルの内容...
-
マイナス同士の前年比
-
【エクセル】指定したセルに入...
-
Excel 分数の分子を小数点で表...
-
エクセルで10分ごとの時刻の...
-
Excel書式設定が24時間以上の設...
-
EXCELのセル上のURLを...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
A1セルに入力したら、入力時間...
-
Excelで同じセルに箇条書きをし...
-
参照先セルに値が入っていない...
-
IF関数で0より大きい数値が入力...
-
エクセルで既に入力してある文...
-
Excel:文字と数字の組合せ、次...
-
空白でないセルの値を返す方法...
-
値を入力後、自動的にアクティ...
-
エクセルで時間の判定をしてい...
-
yyyy/mm/ddからyyyy/mmへの変換
-
Excelでセルに入力されたカラー...
-
エクセルの数式がかぶって、選...
-
エクセルで、特定のセルの内容...
-
エクセルで複数の条件で掛け算...
-
マイナス同士の前年比
-
入力したところまでを自動的に...
-
EXCELのセル上のURLを...
-
excel 関数にて文字を0として認...
-
バーコードが読み取れない原因...
-
ExcelVBAでセルを編集状態にす...
おすすめ情報