エクセルで時間の計算について

質問

時給計算する為の表を作成しています。
時給は時間帯で変るので、時間帯ごとの時間数が出るように計算したいのですが、
自分で考えると条件式を多用して複雑になってしまいます。
もっと簡単に出来るアイディアはありませんか？

具体的な内容です。
6:00～12:00の間は900円（Ａ時間帯）
12:00～17:00の間は800円（Ｂ時間帯）
17:00～20:00の間は900円（Ａ時間帯）
勤務開始及び終了時刻はは6:00～20:00までの間、日によってバラバラです。
データの入力は勤務開始時刻と終了時刻を入力します。
そのうえで、Ａ時間帯が○時間（午前と夕方以降の合計）、Ｂ時間帯が△時間という結果を得る為の
良い方法があれば教えてください。

回答No.3

zap35

A1に開始時刻、B1に終了時刻があるとき
　6:00～12:00の勤務時間　→　　=IF(A1<"12:00"*1,MIN(B1,"12:00"*1)-A1,0)
　12:00～17:00の勤務時間　→　=IF(A1<"17:00"*1,MIN(B1,"17:00"*1)-MAX(A1,"12:00"*1),0)
　17:00～20:00の勤務時間　→　=IF(B1>="17:00"*1,B1-MAX(A1,"17:00"*1),0)
でそれぞれ求めることができます。まずこの式を結果を確認してください。

次に時給を計算するためには「3:30」の時刻表示を「3.5」のような十進数に変換する必要がありますが、これは簡単です。「3:30」のセルに数字の「24」を掛ければよいのです。ですからそれぞれのシフトの給与は

　6:00～12:00の給与　→　　=IF(A1<"12:00"*1,(MIN(B1,"12:00"*1)-A1)*24*900,0)
　12:00～17:00の給与　→　=IF(A1<"17:00"*1,(MIN(B1,"17:00"*1)-MAX(A1,"12:00"*1))*24*800,0)
　17:00～20:00の給与　→　=IF(B1>="17:00"*1,(B1-MAX(A1,"17:00"*1))*24*900,0)
になります。一日の給与はこの３つを足せばよいですね。
一行にまとめるもよし、セルを分けてそれぞれのシフト毎に計算してSUM関数で合計するもよし…

補足
・エクセルでは１日をシリアル値の「1」で表し、１時間は「1/24」で表されます（これが日付や時刻を「標準」書式で表示したときの値です）。従って時刻表示は24倍することで十進表記に変更できます
・式中の「"12:00"*1」は時刻の文字列をシリアル値に変換する方法の一つです。「*1」って何だろうと悩まず、「そんなもんだ」と思ってください

回答No.2

mshr1962

A1が始業、B1が終業として
B時間=MAX(MIN(B1,17/24),12/24)-MIN(MAX(A1,12/24),17/24)
A時間=B1-A1-B時間

回答No.1

EX-STAR999

確かにセルの計算式のみで行う場合は、１つのセルだけで計算しようとすると複雑になります。この場合は時間帯を３つの列に分けて計算してはいかがでしょうか？

6:00～12:00の間は900円（Ａ時間帯）
12:00～17:00の間は800円（Ｂ時間帯）
17:00～20:00の間は900円（Ｃ時間帯）

１日の勤務開始及び終了時刻を記入しているのであれば、それぞれの時間帯を勤務時間を条件設定して、列毎に時間と時給合計を計算して、それを合計するのはどうでしょうか？
この方法なら条件式もそれほど複雑にならなくて済みます。

他にはＶＢＡを使うことも考えられますが、ＶＢＡの知識がないとできないのでここでは省略します。