必要なデータ
名前、出勤時間、退勤期間、休憩時間
※残業は申請が必要、残業理由の記載も必要
※土日祝はお休みでこちらの会社は休日出勤はありません
必要項目を入力しましょう
A1[名前]、B1[出勤時間]、C1[退勤時間]、D1[休憩時間]と入力してください。
A2[博多哲也]、B2[9:00]、C2[18:00]、D2[1:00]と入力してください。
A3[日付]、B3[曜日]、C3[打刻(出勤)]、D3[打刻(退勤)]、E3[残業申請]、F3[出勤時間]、G3[退勤時間]、H3[休憩]、I3[労働時間]、J3[残業時間]と入力してください。
日付と曜日を表示するために関数を入力しましょう
A4[2023/1/1]と入力してください。
A5[=A4+1]と入力してください。
※([=]を入力→[A4]をクリック→[+1]を入力でもＯＫ)
[A5]をクリック→セルの右下にカーソルを合わせる→下までドラッグして[A34]の2023/1/31の所までコピーする
※日付に29日以降が無い場合は[-]を入力をしたい方はリンク先をご確認ください。
B4[=TEXT(A4,”aaa”)]と入力してください。
※([=A4]を入力→[右クリック]→[書式設定]→[表示形式/ユーザー定義]→種類に[aaa]と入力→[OK]をクリック)
[B4]をクリック→セルの右下にカーソルを合わせる→下までドラッグして[B34]の所までコピーする
出勤時間(業務開始時間)を表示する関数を入力しましょう
[F4]に[=IF(C4=””,””,IF(C4<=$B$2,$B$2,CEILING(C4,”0:15″)))]と入力してください。
関数の説明
[C4]が空白なら空白
C4=””,””
[B2]の[9:00]が出勤時間(業務開始時間)
[C4]が[B2]より早い時間であれば、[9:00]になります。
C4<=$B$2,$B$2
[C4]が[B2]より遅ければ遅刻となり、5分単位で時間を区切ります。
CEILING(C4,”0:15″)
※5分単位で切り上げ、切り捨てをする方法の詳細を知りたい場合はリンク先をご確認ください。
残業申請に[〇]を入力するようにしましょう
[E4]にプルダウンで[〇]を入力できるようにします
[E4]をクリック→メニューバー[データ]→[データの入力規則]→[設定][入力値の種類/リスト][元の値/〇]→[入力メッセージ]をクリック→[タイトル/残業申請][入力時メッセージ/プルダウンで「〇」を選択]と入力→ＯＫ
退勤時間(業務終了時間)を表示する関数を入力しましょう
[G4]に[=IF(D4=””,””,IF(E4=”〇”,FLOOR(D4,”0:15″),IF(D4>=$C$2,$C$2,FLOOR(D4,”0:15″))))]と
もしくはORを使い
[=IF(D4=””,””,IF(OR(E4=”〇”,D4<=$C$2),FLOOR(D4,”0:15″),$C$2))]
入力してください。
関数の説明
[D4]が空白なら空白
D4=””,””
[C2]の[18:00]が退勤時間(業務終了時間)
[G4]が[C2]より遅い時間であれば、[18:00]になります。
D4>=$C$2,$C$2
[D4]が[C2]より早ければ早退となり、[D4]5分単位で時間を区切ります。
FLOOR(D4,”0:15″)
[E4]に[〇]がある場合は[残業]となり、[D4]5分単位で時間を区切ります。
E4=”〇”,FLOOR(D4,”0:15″)
※5分単位で切り上げ、切り捨てをする方法の詳細を知りたい場合はリンク先をご確認ください。
ORを使った場合
[E4]に[〇]がある場合は[残業]、もしくは[D4]が[C2]より早ければ早退、[D4]5分単位で時間を区切ります。
それ以外は[18:00]を表示します。
IF(OR(E4=”〇”,D4<=$C$2),FLOOR(D4,”0:15″),$C$2))
休憩時間を表示する関数を入力しましょう
出勤すれば[1:00]の休憩が必ずある
[C4]が空白で無かったら[D2]を表示する
=IF(C4=””,””,$D$2)
労働時間を表示する関数を入力しましょう
労働時間[E2]に[8:00]と表示されています。
[8:00]以上なら[8:00]それ以外は[退勤時間-出勤時間-休憩時間]となります。
[I4]に
=IF(C4=””,””,IF(G4-F4-$D$2>=$E$2,$E$2,G4-F4-$D$2))
を入力してください
関数の説明
[C4]が空白なら空白
C4=””,””
[退勤時間]-[出勤時間]-[休憩]が[労働時間]より大きい場合は[労働時間]を表示する
[G4]-[F4]-[D2]が[E2]より大きい場合は[E2]を表示する
G4-F4-$D$2>=$E$2
それ以外
G4-F4-$D$2
残業時間を表示する関数を入力しましょう
[退勤時間-出勤時間-休憩時間-労働時間]が[8:00]以上なら[残業時間]を表示
[J4]に
=IF(C4=””,””,IF(G4-F4-H4<=$E$2,””,G4-F4-H4-$E$2))
を入力してください
関数の説明
[C4]が空白なら空白
C4=””,””
[退勤時間-出勤時間-休憩時間-労働時間]が[8:00]以上なら[残業時間]を表示する
=IF(G4-F4-H4<=$E$2,””,G4-F4-H4-$E$2)
次回は打刻のフォームをＶＢＡで作ります。
関連リンク
【EXCEL関数】日付に29日以降が無い場合は[-]を入力 翌月1日を表示する
EXCEL関数 5分単位で切り上げ、切り捨てをする方法
経緯説明
このタイムシートはお客様の要望により、派遣社員さん用に作成したものです。
EXCELの授業にて「タイムシート」を作りたい
打刻もパソコンでできるようにしたいと申し出を受けました。
週1回で3ヶ月の授業を申し込んでいただきました。
授業の復讐を兼ねて少しづつアップしていきます。
更新履歴
3/24作成開始
スポンサーリンク
コメント