関数がわからなくて困っています。
基本は8:30~17:30
残業(早出含む)とされる時間帯
5:00~7:30/17:30~19:30 この時間帯は1.0時間刻み
(退社が18:00だと残業は0)
7:30~8:30/19:30~24:00 この時間帯は0.5時間刻み
(退社が20:00だと残業は2.5)
休憩は1:00(12:00~13:00)
以上の条件でC列―出社時刻、D列―退社時刻、
E列―勤務時間、F列―残業時間
で表を作った場合、F列の残業時間を出す式がわかりません。
(E列の勤務時間もですね。自分で考えたのはアヤシイ)
E/F列は、例えば1時間30分なら1.5と出てくるようにします。
7:30出社で20:00退社の場合、勤務時間は8.0、残業時間は3.5となる
ようにしたいのです。
時間の計算が苦手で・・・。どなたか教えてください。
No.6ベストアンサー
- 回答日時:
No.5です。
すいません、条件のはっきりしない部分もありましたので十分な検証をしていませんでした。
(やはり、17:30より早い時間を入力するケースなどがあるのですね。)
どちらにしましても一部式もおかしかったので、F2の式は下記に訂正を
致します。
E2 =(MIN("17:30",D4)-MAX(C4,"8:30")
-IF(C4-"13:00"<0,"13:00"-MAX(C4,"12:00"),0))*24
一行につなげて入力してください。
1行目部分はトータル勤務時間、2行目が昼休み分の引き算です。
条件がありませんので、勤務時間は1分単位で計算しています。
F2 =(IF("7:30"-C3>0,FLOOR("7:30"-MAX(C3,"5:00"),"1:00"),0)
+IF("8:30"-C3>0,FLOOR("8:30"-MAX(C3,"7:30"),"0:30"),0)
+IF(D3-"17:30">0,FLOOR(MIN(D3,"19:30")-"17:30","1:00"),0)
+IF(D3-"19:30">0,FLOOR(MIN(D3,"24:00")-"19:30","0:30"),0))*24
一行につなげて入力してください。
各行は順に、5:00~7:30、7:30~8:30、17:30~19:30、19:30~24:00の
残業時間に対応していますので、作業列として分けた方が
分かりやすいかもしれません。
遅くなりましたがありがとうございました。
その後条件が変わったり、新しい表が出てきたりしましたがなんとか形になりました。
変更条件―深夜勤務22:00~5:00(30分単位)休憩時間24:00~1:00
私の記入間違いですが、5:00~7:30は30分単位、7:30~8:30は60分単位でした。
新しい表というのはF列-普通残業・G列-深夜残業・H列-早出の項目が出ている表で、人によって使い分けるそうです。
教えて頂いた式を使って深夜残業の列にIF(COUNT(C3:D3)=2,(IFD3-"22:00">0,FLOOR(MIN(D3,"29:00)-"22:00","0:30"),0)-IF(OR(C3>"24:00",D3<"25:00">0),0,"1:00"))*24,"")と入れました。
全てのトータル残業はF2の式で教えて頂いたものに時間を変えて、深夜分をプラスしてできました。
早出分は"0:30"と"1:00"を入れ替えてやりましたが、7:00出勤と入れると1.0と出てきてしまい30分少なくなってしまいましたのでFLOORをCEILINGに変えたら上手くいきました。
しかし私の仕事でもないのにやらされて、「まだ?」と文句まで言われて疲れました。でもエクセルの勉強をしたので我慢しましょう。
No.5
- 回答日時:
細かい条件が不明ですが(5:00より早い、24:00より遅い場合)、
とりあえずご質問の範囲内で式を作ってみました。
E2=(MIN("17:30",D2)-MAX(C2,"8:30")-"1:00")*24
F2=(FLOOR("8:30"-C2,"1:00")+IF(D2-"19:30">0,FLOOR(D2-"19:30","0:30")+"2:00",FLOOR(D2-"17:30","1:00")))*24
ご参考まで。
この回答への補足
ありがとうございます。
その通りに入力してみたら上手くいきましたが、出社時間が7:30で退社時間が16:00の場合に、残業時間にエラーがでてしまいました。
条件は会社の規則によるとその通りになってます。
(5:00より早い、24:00より遅いのは無しです)
No.3
- 回答日時:
まだ勉強中なので 参考になればと思い
条件が複雑なので
できれば残業表のようなものを 別に作り
そこから 持ってくる形は どうでしょうか?
表さえ作れば
F2セルに
=VLOOKUP("8:30"-C2,早出残業分,2,1)+VLOOKUP(D2,残業分,2,1)
表は2つ
1個目は 早出分
1列目に 8:30よりどれくらい早いか
("8:30"-出勤時間)
2列目は その時の会社規程の残業時間
2個目は 残業分
1列目は 時間そのまま
2列目は その時の会社規程の残業時間
そして 表には 名前をそれぞれつける(名前の定義)
私も勉強中なので大変参考になりました。
式というのは考え方によってどんな形にもできる物なんですね。
現在試行中です。
ご丁寧にありがとうございました。
No.2
- 回答日時:
残業時間の方はK1に
=IF(I1>=TIMEVALUE("8:00"),I1-TIMEVALUE("8:00"),0)
で計算できます。
これで、「勤務時間」と「残業時間」は計算できましたが、ご質問の様に「○○時間」と計算するにはHOUR関数を使います。
E1に
=HOUR(J1)
F1に
=HOUR(K1)
としますと、「○○時間」が得られます。
しかし、分単位は切り捨てになるので、残業時間の「3.5」は得られません。そこで、
F1を
=HOUR(K1*2)/2
としますと、30分(1/2時間)単位での切り捨てになります。
!!!!!注意!!!!!
時間の計算はエクセル内部の「誤差」によって思った結果にならないことがあります。
運用前に十分にテストしてください。
!!!!!注意!!!!!
2回に渡ってのご説明ありがとうございます。
それも注釈までつけていただいてわかり易かったです。
現在試している所です。
関数の本をみてやっていたのですが、本の通りの条件じゃない場合に
困ってしまって。
No.1
- 回答日時:
はじめまして
途中の結果が分かるように、作業用の列を使いながら説明します。
C1に出社時刻、D1に退社時刻が入っているとして、説明します。
まず、「勤務時間」からです。
これは単純にI1に
=D1-C1-"1:00"
で得られます。(休憩時間の1時間を引いています)
ただ、ご質問では、8時間以内を「勤務」時間、それを越えたら「残業時間」とされたいようですので、
J1に
=IF(I1>=TIMEVALUE("8:00"),TIMEVALUE("8:00"),I1)
とします。
計算結果が0.3333…のような少数になってしまった場合には、セルの書式を「時刻」にしてください。
ただし、休憩時間をはさんで勤務したかを考慮していないので、一律に1時間引いています。
数式で判断することもできますが、午前中だけ出社して、その間の休憩時間が10分というような場合には、手入力で余分に引いた50分プラスするセルをもうけておいた方が簡単だと思います。
ここでTIMEVALUEという関数が出てきました。
エクセルでは日付や時刻を「シリアル値」という数値に置き換えています。
J1の計算ではエクセルが「これは時刻だ!」と判断して計算しましたが、関数の中では上手く判断できないこともあります。そのときには、時刻を「シリアル値」に変換してから計算します。
シリアル値については以下のサイトを参照してください。
http://support.microsoft.com/kb/882391/ja
http://support.microsoft.com/kb/880979/ja
http://support.microsoft.com/kb/401659/ja
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- 就職 ホワイト企業ですか? 3 2023/03/10 15:16
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- 就職・退職 入社1年目の会社がサービス残業ひどいので退職と同時に請求しようと思うのですが、そのためには証拠が必要 3 2023/06/01 12:56
- その他(ビジネス・キャリア) 強制残業について 3 2022/08/12 19:49
- 転職 現在転職活動中で2社候補があるのですが どちらに行くかもしくは現職に残るか迷っていて 参考までに皆様 2 2022/09/29 19:22
- 人事・法務・広報 みなし残業と深夜の割増賃金 1 2023/04/17 00:06
- Excel(エクセル) エクセルの時間の計算、何時以下の場合の足し引きに苦戦しています。 2 2022/11/16 16:39
- 正社員 手取り15万円 夜勤あり工場勤務です 13 2022/09/27 15:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
変形四角形の面積を計算するソフト
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルVBA、別ブックへ転記す...
-
【EXCEL】条件に一致した最新デ...
-
エクセルで二つの数字の小さい...
-
エクセルで時刻(8:00~20:00)...
-
PowerPointで表の1つの列だけ...
-
SUMIFとCOUNTIFを合わせたよう...
-
エクセル 文字数 多い順 並...
-
エクセルでオートフィルタのボ...
-
2つのエクセルのデータを同じよ...
-
エクセル(勝手に太字になる)
-
Excelのオートフィルタで非表示...
-
エクセル 同じ値を探して隣の...
-
エクセルでの複数条件下での標...
-
Excel関数で、範囲内の最後のセ...
-
エクセルの並び変えで、空白セ...
-
エクセルで、2種類のデータを...
-
エクセルのオートフィルタで最...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで数字を入力すると自...
-
エクセル 重なる時間の計算方法
-
JWCAD勾配入力方法
-
回転体の体積を求める方法
-
エクセルで残業時間を時間帯に...
-
勤怠管理で始業時刻を基準に計...
-
a1=1/5,an+1=an/4an-1 おしえて...
-
エクセルで1:00の90分後...
-
Excelでこんな関数ありませんか...
-
DRA-CADの計算機能につ...
-
変形四角形の面積を計算するソフト
-
(int)/(int)=(double)にする方法
-
Excelについて教えてください。...
-
エクセルで年月期間を4桁表示...
-
エクセルの枠の複数選択
-
mysqlで15分きざみで現在時刻を...
-
数量3.450 単価28.5 で40%を引...
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで二つの数字の小さい...
おすすめ情報