関数がわからなくて困っています。
基本は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で質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
回転体の体積を求める方法
-
エクセル 重なる時間の計算方法
-
EXCELで2つの数値のうち大きい...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで時刻(8:00~20:00)...
-
エクセルでオートフィルタのボ...
-
Excelで半角の文字を含むセルを...
-
エクセル 文字数 多い順 並...
-
エクセルで最初のスペースまで...
-
SUMIFとCOUNTIFを合わせたよう...
-
エクセルで特定の文字が入って...
-
エクセルのオートフィルタで最...
-
エクセルで、2種類のデータを...
-
Excel 文字列を結合するときに...
-
「B列が日曜の場合」C列に/...
-
Excelのオートフィルタで非表示...
-
エクセルの関数(日数の平均の...
-
ある一定時間を超えた場合の超...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセル 重なる時間の計算方法
-
エクセルで数字を入力すると自...
-
Excelでこんな関数ありませんか...
-
JWCAD勾配入力方法
-
回転体の体積を求める方法
-
a1=1/5,an+1=an/4an-1 おしえて...
-
勤怠管理で始業時刻を基準に計...
-
変形四角形の面積を計算するソフト
-
DRA-CADの計算機能につ...
-
勤務表で時間の切り捨てをした...
-
エクセルで残業時間を時間帯に...
-
下記内容が当てはまった時給計...
-
見た目秒数になっているシリア...
-
エクセルで年月期間を4桁表示...
-
エクセル関数で深夜勤務の計算
-
Excelについて教えてください。...
-
Excel関数で小数点になる前まで...
-
ファイルメーカー(File Maker...
-
mysqlで15分きざみで現在時刻を...
-
エクセルで1:00の90分後...
おすすめ情報