勤怠管理で始業時刻を基準に計算させるには?
エクセルで勤怠管理表を作成するにあたって、例えば8時30分が始業時刻とする場合、社員は8:05にタイムカードに記録した場合や7:58などに記録した場合は、始業時刻より余計な時間分も勤務時間を計算する上でふくまれてしまいます。とにかく8:30より早く出勤する場合でも、管理上セルの値は8:30になるように変換したいです。また、万一、7:20に記録した場合をセルに入力した場合は、1時間単位の7:30に変換したいです(朝1時間、時間外勤務を計算させるため)。
社員が全員、8:30に記録してくれたら、エクセル上の計算はたやすいのですが、出勤時間は皆、バラバラなので始業時間前の何分かも勤務時間を計算するときに余計に加算されるため、あくまでも始業時刻の8:30を基準に計算させるためには、関数式をどう言う風に工夫すればよいですか?
それとも、関数式では無理ですか?なお、終業時刻についても同じ理屈です。
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
1時間とか30分とか切り捨てると、59分とか29分とかの切り捨てになるので、
テキストでは、15分単位で、CEILING関数(基準値の倍数の内、最も近い値に切り上げる)とFLOOR関数(基準値の倍数の内、最も近い値に切り捨てる)を使うみたいですよ。
=CEILING(A2,1/96)
=FLOOR(B2,1/96)
上の式を横の列に別表にすると、訂正された数値が判断できます。
シリアル値では、
A2→CEILING(A2,1/96)
B2→FLOOR(B2,1/96)
=IF(COUNT(A2:B2)<2,"",FLOOR(B2,1/96)-CEILING(A2,1/96)-MAX(0,MIN(77/144,FLOOR(B2,1/96))-MAX(1/2,CEILING(A2,1/96)))-MAX(0,MIN(91/144,FLOOR(B2,1/96))-MAX(5/8,CEILING(A2,1/96))))
時刻をそのまま使う場合は、
A2→CEILING(A2,"0:15")
B2→FLOOR(B2,"0:15")
=IF(COUNT(A9:B9)<2,"",FLOOR(B9,"0:15")-CEILING(A9,"0:15")-MAX(0,MIN("12:50",FLOOR(B9,"0:15"))-MAX("12:00",CEILING(A9,"0:15")))-MAX(0,MIN("15:10",FLOOR(B9,"0:15"))-MAX("15:00",CEILING(A9,"0:15"))))
どちらの場合も、表示形式はh:mm
ありがとうございます。なるほどよくわかります。一時、ROUNDとかもら考えましたが、うまくいかず、この関数なら上手くいきました。本当にご親切な方で感謝致します。ありがとうございました。
No.2
- 回答日時:
No1です。
>遅刻など時間がとれなくなるので、困っています。
シリアル値を用いているのなら、max関数で遅い方を採用すれば良いだけでしょう。
ありがとうございます。
>MAX関数の遅い方
がよくわかりませんが、繰り上げ繰り下げなども考えましたが私のレベルでは難解です。ありがとうございました。
No.1
- 回答日時:
こんばんは
ご質問の意味がよくわかりませんけれど・・
「入力値が何時になっていようが、8:30と見做したい」というように読めます。
であるなら、入力値のセルを参照する代わりに、"8:30"の固定値を使用すれば良いだけでしょう。
「勤怠管理」とのご質問ですが、限りなくブラックな臭いがしますね・・
ありがとうございます。固定値にしてしまうと、遅刻など時間がとれなくなるので、困っています。例えば8:00.〜17:00で8時間勤務なら、実際は7:50入〜17:10出なら勤務時間8時間のところ、計算上、勤務時間が8時間20分になってしまうと言うことです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルで作った勤怠表に発生する時間の誤差の修正方法を教えていただけないでしょうか? A1セルに出社 6 2023/12/29 13:09
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- その他(ビジネス・キャリア) タイムカードの打刻についてお聞きしたいです。 私が今勤めている会社(130人程度の中小企業です)では 9 2023/12/07 19:23
- Excel(エクセル) エクセルの早退時間を計算したいです。計算はうまくいっているようですが… 1 2022/12/26 16:22
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- Excel(エクセル) 勤務時間を割り出す、関数式を教えて頂きたいのですが、少し複雑で例えば項目名としてA1セルに出社時刻と 3 2023/12/25 21:05
- 労働相談 飲食店勤務のスタッフの勤怠管理について 新規事業で飲食店を始めたのですが、深夜営業もあり勤怠管理が難 2 2023/04/23 15:56
- Excel(エクセル) エクセルで勤務時間の計算をしています。 下記図でstartは勤務開始時間、endは勤務終了時間です。 5 2022/06/07 13:51
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
Excelの共有ファイルについて質問です。
Excel(エクセル)
-
Excelでこういう年を切り替えられる簡易なカレンダーを作ったのですが、 年や月を変えると、色が付い
Excel(エクセル)
-
Excel関数かなにかでスムーズに処理するにはあなたならどうしますか?
Excel(エクセル)
-
-
4
エクセルで作った勤怠表に発生する時間の誤差の修正方法を教えていただけないでしょうか? A1セルに出社
Excel(エクセル)
-
5
VBA(えくせる)ってなんでメンテできない人が多いんですか?
Excel(エクセル)
-
6
エクセルで⑤番の操作が分かりません。どういう関数が良いんですか?
Excel(エクセル)
-
7
excelについて。
Excel(エクセル)
-
8
複数エクセルファイルの合成
Excel(エクセル)
-
9
エクセルで相対パスの書き方を教えてください
Excel(エクセル)
-
10
たくさんの計算セルに一括で同一の関数を追加したいです。
Excel(エクセル)
-
11
エクセルでの順番入れ替え
Excel(エクセル)
-
12
エクセルのセル統合について
Excel(エクセル)
-
13
Excelの関数に関する質問です
Excel(エクセル)
-
14
エクセルで添付画像のように値を入力する方法はありますか?
Excel(エクセル)
-
15
Excel 数行分セル結合したあと、結合した一番下の行を非表示にしたら、罫線が印刷されません。
Excel(エクセル)
-
16
Excelで【1-11】と入力すると【1月11日】になってしまう
Excel(エクセル)
-
17
マクロを実行するとエクセルが小さく表示される
Excel(エクセル)
-
18
エクセルの関数でわからないことあるのでコード付きで教えてください
Excel(エクセル)
-
19
Excelの関数で教えください。 今作成している表で 例えば A1=D1 A2=D2としてD1に神奈
Excel(エクセル)
-
20
【マクロ】for nextステートメントがわかりません。例あり。
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル 重なる時間の計算方法
-
エクセルで数字を入力すると自...
-
JWCAD勾配入力方法
-
エクセルで1:00の90分後...
-
エクセルで残業時間を時間帯に...
-
EXCELで2つの数値のうち大きい...
-
文字列になっている時間をVBAで...
-
エクセルで二つの数字の小さい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで時刻(8:00~20:00)...
-
SUMIFとCOUNTIFを合わせたよう...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
「B列が日曜の場合」C列に/...
-
エクセルで、2種類のデータを...
-
エクセルでオートフィルタのボ...
-
【エクセル】区切り位置で分割...
-
2つのエクセルのデータを同じよ...
-
エクセル(勝手に太字になる)
-
桁の違う数値での並び替え
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで数字を入力すると自...
-
エクセル 重なる時間の計算方法
-
JWCAD勾配入力方法
-
回転体の体積を求める方法
-
エクセルで残業時間を時間帯に...
-
勤怠管理で始業時刻を基準に計...
-
エクセルで1:00の90分後...
-
a1=1/5,an+1=an/4an-1 おしえて...
-
DRA-CADの計算機能につ...
-
Excelでこんな関数ありませんか...
-
変形四角形の面積を計算するソフト
-
(int)/(int)=(double)にする方法
-
Excelについて教えてください。...
-
エクセルで年月期間を4桁表示...
-
mysqlで15分きざみで現在時刻を...
-
エクセルの枠の複数選択
-
数量3.450 単価28.5 で40%を引...
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで二つの数字の小さい...
おすすめ情報