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

質問

エクセルで勤怠管理表を作ろうとしていますが、わからない部分がありますのでご教授ください。

セルの書式をh:mmとして退社時間から出勤時間を引けば実際に勤務した時間が算出されるというところまでは出来たのですが、以下に挙げた二つの機能を作るにはどうしたらよいのでしょうか？

１．デフォルトで12:00から13:00、22:00から22:30を休憩時間としたいため、その時間を挟んで勤務した場合には、自動的に勤務時間からその時間を引きたい。
　例）10:00から18:00まで勤務した場合、休憩時間を1時間引いて算出勤務時間を7時間としたい。

２．日をまたいだ勤務形態だと、うまく時間が算出されないので、正しく算出したい。
　例）21:00に出社して、翌日の05:00まで勤務した場合、休憩時間を30分引いて算出勤務時間を8時間30分としたい。

セルに直接計算式を書くより、マクロを使ってうまく出来ればベターだと思っているのですが、良い方法がありましたらご教授ください。よろしくお願いいたします。

回答No.1

BLUEPIXY · ベストアンサー

標準モジュールで
Public Function 勤務時間(出勤時間 As Date, 退社時間 As Date) As Date
Dim 休憩時間 As Date
休憩時間 = 0

If 出勤時間 > 退社時間 Then
退社時間 = 退社時間 + 1
End If
If 出勤時間 <= TimeValue("12:00:00") And TimeValue("13:00:00") <= 退社時間 Then
休憩時間 = 休憩時間 + TimeValue("01:00:00")
End If
If 出勤時間 <= TimeValue("22:00:00") And TimeValue("22:30:00") <= 退社時間 Then
休憩時間 = 休憩時間 + TimeValue("00:30:00")
End If
勤務時間 = 退社時間 - 出勤時間 - 休憩時間
End Function
として適当なセルで
=勤務時間(出勤時間セル,退社時間セル)
とする

お礼

思い通りのものが出来上がりました。
ありがとうございました。完璧です。

補足

ありがとうございます。試したらまた結果をご報告いたします。

回答No.4

mee42760

#2です。
シリアル値に変更し忘れていました。
#1様。ご指摘ありがとうございました。
ご質問者様、すいませんでした。

回答No.3

BLUEPIXY

＃２様の２４は、多分１の間違い

回答No.2

mee42760

VBAがご希望ということでしたが、一応、セルに直接書き込む計算式を作って見ました。
ご参考まで・・・。

=IF(A1<B1,B1-A1,B1+24-A1)-IF(AND(A1<0.5,B1>0.541667),0.041667,0)-IF(AND(A1<0.916667,IF(A1<B1,B1>0.9375,B1+24>0.9375)),0.020833,0)
※セルA1出勤時間、セルB1退勤時間です。

補足

ありがとうございます。試したらまた結果をご報告いたします。