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ベストアンサー
- 回答日時:
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
------ ここまで ------
No.3
- 回答日時:
前提条件
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
- 回答日時:
「祝祭日も除く」という条件があるので難しくなっています。
基本的には、
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さん、回答有難うございます。
今回何の為の計算かといいますと、コールセンターの回答を受けてから
回答を返すまでのリードタイムを出したいと思っています。
単純な時間計算にしてしまうと、業務終了間際にきたものを翌日に回答した場合、夜中の時間も足されたものになってしまうので
困っていました。
あまり知識がないので、まずは回答を読み込んでみたいと思います。
取り急ぎお礼まで
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ExcelにてA1セルに1、B1セルに月と入力した際に、A列に日付、B列に曜日が入力される様になる為 1 2023/03/02 06:47
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) EXCELピボットテーブル関数について 2 2023/04/10 20:35
- 労働相談 有給計算について 回答お願いします。 建設業経営です。休日は日曜のみ 従業員の有給計算について 雇い 2 2023/03/26 06:12
- Excel(エクセル) 至急です><Excelの関数を教えてください。 2 2022/03/22 17:56
- Excel(エクセル) 現時点の年齢を算出して、その年齢と一致したセルを色付けしたい。 4 2022/06/23 17:49
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
- Excel(エクセル) 月間シフト表から総勤務時間を計算する関数 4 2023/05/20 07:12
- 人事・法務・広報 4月からの残業60時間以上の割増の計算式 2 2023/03/14 14:44
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
行数が不規則な一週間ごとの合...
-
文字列になっている時間をVBAで...
-
ある列、或いは、ある行のセル...
-
マクロの有効化するダイヤログ...
-
【マクロ】フォルダからエクセ...
-
Excelでの時間帯の入力
-
excel で二つのどちらかを選ぶ
-
エクセルの順位別一覧表の自動...
-
Excel 2019 のピボットテーブル...
-
エクセル 価格表から単価を呼び...
-
excelの不要な行の削除ができな...
-
エクセルで特定の範囲内から小...
-
シートAで横に並んだ項目→シー...
-
Excelが固まってしまった。
-
Excel2013のF6キー操作について
-
【関数】スペースがいくつ入っ...
-
Excelはなんで先頭の0を消すん...
-
【Microsoft Office Excel Comp...
-
西暦や和暦の表示をyyyymmdd表...
-
別シートからの文字を変更
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報