Excelでの経過時間の計算について教えてください

質問

Excelで経過時間の計算をしたいです。

A1セルには　2007/10/1 10:31:57
B1セルには　2007/10/3 17:01:32
とはいっている時に以下の考慮をした計算をしたいです。

1.稼動時間帯の範囲内の経過時間で計算したい
一日の稼動時間帯は9:00から20:00(20:01から8:59までは計算対象外にしたい)
　なので、A1セルの分は20:00-10:31=9:29を計算に使用したい

2.営業日を考慮したい(日本の祝日と土曜、日曜)
　営業日以外は計算から外したい

以上が質問内容です、よろしくお願いします。

回答No.2

Nii · ベストアンサー

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

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

祝祭日に関しては、
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
------　ここまで　------

お礼

詳細に教えていたたぎ助かりました。

有難うございました。

回答No.3

Nii

前提条件
　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＊

お礼

ご丁寧に有難うございました。
助かりました！

回答No.1

noname#79209

「祝祭日も除く」という条件があるので難しくなっています。
基本的には、
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日や年末の仕事納め以降も考慮する必要があります。
また、休日出勤、遅出、早退、遅刻、残業、昼休み、休憩時間はどうするのか等、他にも考慮する事項がありそうですね。

お礼

有難うございました。

助かりました。

どうにかそれらしいものが出来たのでこれから使っていきたいと思います。

補足

vizzarさん、回答有難うございます。
今回何の為の計算かといいますと、コールセンターの回答を受けてから
回答を返すまでのリードタイムを出したいと思っています。

単純な時間計算にしてしまうと、業務終了間際にきたものを翌日に回答した場合、夜中の時間も足されたものになってしまうので
困っていました。

あまり知識がないので、まずは回答を読み込んでみたいと思います。

取り急ぎお礼まで