EXCELで、タイムシートと時給計算

質問

タイムシートでは、「始業時刻」「終業時刻」「休憩時間」「８時間以内」「８時間越」「22：00以降」とあります。
　分の丸めは、１５分単位です。
　単純に勤務時間の計算までは、いろんなＨＰから探れましたが、「８時間越」～の数式がわかりません。

　また、時給の計算では、「８時間以内」の時給を、「８時間越」の時給を、「22：00以降」の時給をそれぞれ出したいのです。

　いいアドバイスをお願いいたします。

回答No.3

zap35 · ベストアンサー

＃０１です。休憩は考慮していませんし、分かりやすいように補助セルを使用しています。
それぞれのセルの式を理解すれば補助セルは使用しなくても計算が可能です。

以下のようなデータがあるとき
Ａ列　　Ｂ列　　　Ｃ列　　　　　Ｄ列　　　　Ｅ列　　　　Ｆ列　　　Ｇ列
開始　　終了　　補正　　　　　総勤務　　８Ｈ以内　８Ｈ超　　22時以降
9:40　　17:00　　0 17:00　　　7:15　　　　7:15　　　　0:00　　　0:00
8:30　　20:00　　0 20:00　　　11:30　　　8:00　　　　3:30　　　0:00
8:30　　02:00　　1 02:00　　　17:30　　　8:00　　　　9:30　　　4:00
（書式は補正セルのみ　d hh:mm@　としています）

最初に説明しますがEXCEL内部では１日は数値１に換算されます。従って１時間は　1/24 に換算されます。また１５分は1/24÷４＝1/96です。

補正(C2)の式
　=IF(B2<8/24,B2+1,B2)
これは終了が朝の８時前なら終了時刻に＋１（＝翌日の時間にする）ことをしています。

総勤務（D2)
　=FLOOR(C2-A2,1/96)
補助から開始を引いて、15分刻みで切り捨てています。

２２時以降(G2)
　=IF(C2>22/24,FLOOR(C2-22/24,1/96),0)

８Ｈ超(F2)
　=IF(D2-G2>8/24,D2-8/24,0)

８Ｈ以内(E2)
　=IF(D2<=8/24,D2,8/24)

でも実際の計算では8:20などの中途半端な時刻を入力したとき予め8:30に読み替えてから時間計算をするとか、休日は違う計算式を使うとか、色々とルールがあると思います。
上記式では完全に対応できないと思いますが参考にしてください。

お礼

ありがとうございます。上記の方法で思い通りのモノができました。

回答No.2

NCU

> 分の丸めは、１５分単位です。
FLOORかCEILINGで（切捨てか切り上げかでどちらか選択）

>「８時間越」
=MAX(0,実働時間-"8:00")

>「22：00以降」
=MAX(0,終業時刻-"22:00")

実用的なものを作ろうと思えばもっと複雑になるでしょうが、とりあえず上記は単純な例です。

お礼

ありがとうございます。

回答No.1

zap35

補足要求です。
＞、「８時間以内」の時給を、「８時間越」の時給を、「22：00以降」の時給
の定義をはっきり説明していただかないと、期待に添える回答は出せないのではないでしょうか？

また条件が曖昧です。徹夜勤務で09:00～（翌日）10:00まで勤務したとしたら、日またがりはどうやって判断すればよいですか？（どこかのセルに徹夜勤務の情報があれば分かるかもしれませんが、質問からは読み取れません）

一般的には
　「総勤務時間」－「22：00以降」－８ｈ
が「８時間越」になるようにも思うのですが…

補足

早速の回答ありがとうございます。
時給に関してですが、総勤務時間から、８時間までは、＠Ａ、22：00以前で８時間越えを＠Ｂ、22：00以降は＠Ｃということでお分かりいただけますでしょうか？

基本的に出勤時刻は決まっていて、２４時間以上勤務する設定ではありませんが、「総勤務時間」－「22：00以降」－８ｈで大丈夫かと思います。

説明が分かりづらいでしょうがよろしくお願いいたします。