Excelでの経過時間の計算について教えてください - Office系ソフト - 教えて！goo

前提条件
　A1　始業時間（今回は9：00）
　B1　終業時間（今回は20：00）
　C1　終業時間-始業時間の勤務時間（今回は11：00）
　N*　祝日一覧
　A3～受付日＆時間
　B3～回答日＆時間

とりあえず、日付と時間に分解
　C＊　=DATE(YEAR(A＊),MONTH(A＊),DAY(A＊))
　D＊　=TIME(HOUR(A＊),MINUTE(A＊),SECOND(A＊))
　E＊　=DATE(YEAR(B＊),MONTH(B＊),DAY(B＊))
　F＊　=TIME(HOUR(B＊),MINUTE(B＊),SECOND(B＊))
勤務時間外を補正
　G＊　=IF(D＊>$A$1,IF(D＊<$B$1,D＊,$B$1),$A$1)
　H＊　=IF(F＊>$A$1,IF(F＊<$B$1,F＊,$B$1),$A$1)
受付日と回答日の時間を計算
　I＊　=IF(C＊=E＊,H＊-G＊,$B$1-G＊+H＊-$A$1)
経過日数求め、その間の時間を計算し足しこむ
　J＊　=NETWORKDAYS(C＊,E＊,N1:N＊)
　K＊　=IF(J＊>2,(J＊-2)*$C$1,0)
　L＊　=+K＊+I＊

通報する

VBを暫く弄ってないので、思い出すのに時間が掛かりそうなので、とりあえず、参考になりそうなページを抜粋

Excelで日付関連は、http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu …

祝祭日に関しては、
http://koyomi.vis.ne.jp/mainindex.htm

上記と関係ないけど、祝日かどうかをVBで組むと、下記のようになるかな？
昔作ったので、現在の祝日と合致してるか疑問ですが^^;

手元にあるExcel2000の場合ならば
Altキーを押しながらF11キーを押して、VisualBasicを立ち上げる
メニューから「挿入」「標準モジュール」に貼り付け

A1セルには　2007/1/1 って入ってる場合、
A1セルに　=国民の祝日(A1,1)　っていれたら　元日って表示される。

------　ここから　------
Public Function 国民の祝日(今日 As Date, chk_l As Integer) As String
Dim 年 As Long

国民の祝日 = ""
年 = Year(今日)
月日 = Format(今日, "mmdd")

If Format(今日, "yyyymm") < 194807 Then
Exit Function
End If

If 月日 = 101 Then
国民の祝日 = "元日"
Exit Function
End If
If 年 < 2000 And 月日 = 115 Then
国民の祝日 = "成人の日"
Exit Function
ElseIf 年 >= 2000 Then
If 第一月曜(DateSerial(年, 1, 1)) + 7 = 今日 Then
国民の祝日 = "成人の日"
Exit Function
End If
End If
If 年 >= 1966 And 月日 = 211 Then
国民の祝日 = "建国記念の日"
Exit Function
End If
If DateSerial(年, 3, Int(0.24242 * 年 - Int(年 / 4) + 35.84)) = 今日 Then
国民の祝日 = "春分の日"
Exit Function
End If
If 月日 = 503 Then
国民の祝日 = "憲法記念日"
Exit Function
End If
If 月日 = 429 Then
If 年 < 1989 Then
国民の祝日 = "天皇誕生日"
Exit Function
ElseIf 年 >= 1989 And 年 < 2007 Then
国民の祝日 = "みどりの日"
Exit Function
Else
国民の祝日 = "昭和の日"
Exit Function
End If
End If
If 年 >= 2007 And 月日 = 504 Then
国民の祝日 = "みどりの日"
Exit Function
End If
If 月日 = 505 Then
国民の祝日 = "こどもの日"
Exit Function
End If
If 年 >= 1995 Then
If 年 < 2003 And 月日 = 720 Then
国民の祝日 = "海の日"
Exit Function
ElseIf 年 >= 2003 Then
If 第一月曜(DateSerial(年, 7, 1)) + 14 = 今日 Then
国民の祝日 = "海の日"
Exit Function
End If
End If
End If
If 年 >= 1966 Then
If 年 < 2003 And 月日 = 915 Then
国民の祝日 = "敬老の日"
Exit Function
ElseIf 年 >= 2003 Then
If 第一月曜(DateSerial(年, 9, 1)) + 14 = 今日 Then
国民の祝日 = "敬老の日"
Exit Function
End If
End If
End If
If DateSerial(年, 9, Int(0.24204 * 年 - Int(年 / 4) + 39.01)) = 今日 Then
国民の祝日 = "秋分の日"
Exit Function
End If
If 年 >= 1966 Then
If 年 < 2000 And 月日 = 1010 Then
国民の祝日 = "体育の日"
Exit Function
ElseIf 年 >= 2000 Then
If 第一月曜(DateSerial(年, 10, 1)) + 7 = 今日 Then
国民の祝日 = "体育の日"
Exit Function
End If
End If
End If
If 月日 = 1103 Then
国民の祝日 = "文化の日"
Exit Function
End If
If 月日 = 1123 Then
国民の祝日 = "勤労感謝の日"
Exit Function
End If
If 年 >= 1989 And 月日 = 1223 Then
国民の祝日 = "天皇誕生日"
Exit Function
End If
If 今日 = #4/10/1959# Then
国民の祝日 = "皇太子明仁親王の結婚の儀"
Exit Function
End If
If 今日 = #2/24/1989# Then
国民の祝日 = "昭和天皇の大喪の礼"
Exit Function
End If
If 今日 = #11/12/1990# Then
国民の祝日 = "即位礼正殿の儀"
Exit Function
End If
If 今日 = #6/9/1993# Then
国民の祝日 = "皇太子徳仁親王の結婚の儀 "
Exit Function
End If
If chk_l Then
If 今日 > #4/12/1973# Then
If Weekday(今日) = 2 Then
If Len(国民の祝日(今日 - 1, 0)) Then
国民の祝日 = "振替休日"
Exit Function
End If
ElseIf 年 >= 2005 Then
If Weekday(今日) = 3 Then
If Len(国民の祝日(今日 - 1, 0)) Then
If Len(国民の祝日(今日 - 2, 0)) Then
国民の祝日 = "振替休日"
Exit Function
End If
End If
ElseIf Weekday(今日) = 4 Then
If Len(国民の祝日(今日 - 1, 0)) Then
If Len(国民の祝日(今日 - 2, 0)) Then
If Len(国民の祝日(今日 - 3, 0)) Then
国民の祝日 = "振替休日"
Exit Function
End If
End If
End If
End If
End If
End If
If 年 >= 1985 Then
If Len(国民の祝日(今日 - 1, 0)) Then
If Len(国民の祝日(今日 + 1, 0)) Then
国民の祝日 = "国民の休日"
Exit Function
End If
End If
End If
End If

End Function

Public Function 第一月曜(今日 As Date) As Date
今日 = DateSerial(Year(今日), Month(今日), 1)

Do Until Weekday(今日) = 2
今日 = 今日 + 1
Loop

第一月曜 = 今日
End Function
------　ここまで　------

通報する

「祝祭日も除く」という条件があるので難しくなっています。
基本的には、
1.初日の勤務時間を算出する。
2.最終日の勤務時間を算出する
3.2日目から最終日の前日まで就労日を算出する。
4.土日とかぶらない祝祭日の日数を出す。（それ以外の休業日を含む）
5、「（3)－(4)」に11(時間)をかける。
4.（1）+(2)+（5）を算出。
で、いけるのですが、（4）の日数が年によって異なるので非常に算出しにくいのです。

あえて、やるとすれば、
土日でない休業日を別シートにリストしておき、それぞれの日の隣のセルに
=IF(AND($A1>=Sheet1!$A$1,$A1<=Sheet1!$B$1),1,0)
あるいは
=AND($A1>=Sheet1!$A$1,$A1<=Sheet1!$B$1)*1
などとして、これらの数を合計するしかないでしょう。
なお、一般的な祝祭日以外に、御社の創立記念日での休業や正月の2日、3日や年末の仕事納め以降も考慮する必要があります。
また、休日出勤、遅出、早退、遅刻、残業、昼休み、休憩時間はどうするのか等、他にも考慮する事項がありそうですね。

通報する