〔始業〜終業時刻〕間の勤務時間を『勤務区割』別に求めます。
[#VALUE!]
ktWorkingTime( 始業時刻, 終業時刻, 勤務区割, [算出種別],
[算出帯], [Serial] )
返却値 ‥‥‥ Variant(Date 型配列/Date) 型 始業時刻 ‥‥‥ Date 型 (0:00 〜 23:59) 終業時刻 ‥‥‥ Date 型 (0:00 〜 48:00) 勤務区割 ‥‥‥ Variant(Date 型配列/Array配列/Range) 型 算出種別 ‥‥‥ Integer 型 省略可(省略時:0 ) 算出帯 ‥‥‥ Variant(Integer/String) 型 省略可 Serial ‥‥‥ Boolean 型 省略可(省略時:True )
〔始業〜終業時刻〕間の勤務時間を『勤務区割』別に求めます。
日付跨りを考慮して求められる勤務時間は【本日 および 翌日】に分けられます。
得られる結果は、指定によって下記の2種類に分かれます。
・算出種別 : 0(本日) または 1(翌日)
ワークシート用に個々の時間帯の勤務時間を個別に返します。
算出帯で、どの時間帯を算出するのか指定する必要があります。
算出帯で指定する値は【0〜〔勤務区割数−1〕】の範囲内です。
下記の図を参照して下さい。
・算出種別 : 2(配列で受取)
VBA用に2日間(本日〜翌日)を2次元配列で返します。
この場合は、必ずVariant型項目で受け取って下さい。
配列は[Option Base]に関係なく【0オリジン】で作成されます。
第1次元 : 0(本日),1(翌日)
第2次元 : 0〜〔勤務区割数−1〕
下記の図では【結果(0,2)】は【本日の[9:00 〜 12:00]】の値になります。
・算出種別 : 3(集計)
パラメータ「算出帯」で指定した複数の時間帯の勤務時間を合計して返します。
「午前/午後の通常勤務」や「早出/夜間残業」の合計を1回で求められます。
「Serial 」引数を省略もしくは[True ]とすれば、シリアル値(Date 型)で返します。
[False ]を指定すると、分(Long 型)で返します。
パラメータが不正な場合は[#VALUE!]が返ります。
始業時刻 ‥‥‥ 0:00 〜 23:59の範囲の時刻シリアル値を指定します。
終業時刻 ‥‥‥ 0:00 〜 48:00の範囲の時刻シリアル値を指定します。
[始業≧終業]の場合は翌日と判断します。
勤務区割 ‥‥‥ 各勤務時間帯の終了時刻の一覧表を指定します。
セル範囲やArray配列などを指定できます。
詳細は下記解説を参照して下さい。
算出種別 ‥‥‥ 上記「返却値」を参照して下さい。
算出帯 ‥‥‥ 算出種別により指定する内容が異なります。
・算出種別が 0(本日) または 1(翌日)の場合
勤務区割の並びから、どの時間帯を求めるのかを
勤務区割内の順番(先頭はゼロ)で指定します。
・算出種別 が 2(配列で受取)の場合
指定する必要はありません。
・算出種別 が 3(集計)の場合
各時間帯に対して【1:集計する/0:集計しない】を並べて
下記のように文字列で指定します。#文字を間に挟んで
本日/翌日を指定します。
従って、文字数は『2*時間帯数+1』になります。
例) 下記の図で午前/午後の通常勤務の合計
"00101000#00101000"
Serial ‥‥‥ 結果をシリアル値で返す(True /省略)か、 「分」で返す(False )か
を指定します。
【始業時刻≧終業時刻】は日付跨りと判断します。
[8:00 〜 5:00]は[8:00 〜 29:00]と同値です
終業が日付を跨いで且つ始業時刻を越えている場合、終業時刻の指定
は必ず【 24:00 〜 48:00 】の値で行なって下さい。
『勤務区割』には各勤務時間帯の終了時刻を表形式で渡します。
勤務区割の最終時刻は必ず【24:00】にして下さい。
ワークシートで利用する場合は、下記の図のようにセル範囲(1行または1列形式)
を指定します。
VBAから利用する場合は下記のいずれかで指定して下さい。
・ワークシート上に『勤務区割』データを用意して、そのセル範囲を【Range 】で指定する。
Dim vnt結果 As Variant
vnt結果 = ktWorkingTime(始業時刻,終業時刻,Range("B3:B10"),2)
・【Array 関数】を使って指定する。この場合、【24:00】は『数値の1』で定義します。
Dim vnt結果 As Variant
vnt結果 = ktWorkingTime(始業時刻,終業時刻,_
Array(#7:00:00 AM#, #9:00:00 AM#, #12:00:00 PM# _
, #1:00:00 PM#, #5:30:00 PM#, #6:00:00 PM# _
, #10:00:00 PM#, 1),2)
もしくは
Dim vnt結果 As Variant
Dim vnt勤務区割 As Variant
vnt勤務区割 = Array(#7:00:00 AM#, #9:00:00 AM#, #12:00:00 PM# _
, #1:00:00 PM#, #5:30:00 PM#, #6:00:00 PM# _
, #10:00:00 PM#, 1)
vnt結果 = ktWorkingTime(始業時刻,終業時刻,vnt勤務区割,2)
・Array 関数を使わないで、Date型配列を作成して指定する。
この場合、必ず【0オリジン】として下さい。
Dim vnt結果 As Variant
Dim dtm勤務区割(0 To 7) As Date
dtm勤務区割( 0 ) = #7:00:00 AM#
dtm勤務区割( 1 ) = #9:00:00 AM#
:
dtm勤務区割( 6 ) = #10:00:00 PM#
dtm勤務区割( 7 ) = 1
vnt結果 = ktWorkingTime(始業時刻,終業時刻,dtm勤務区割,2)
上の例で、集計オプションを利用すると下記のように求められます。
通常勤務 = ktWorkingTime(B1, C1, B3:B10, 3, "00101000#00101000")
普通残業 = ktWorkingTime(B1, C1, B3:B10, 3, "01000010#01000010")
深夜残業 = ktWorkingTime(B1, C1, B3:B10, 3, "10000001#10000001")
※ 時刻データの小数誤差について
ktWorkingTime 関数では、始業時刻 / 終業時刻 に 小数誤差 を持った時刻データ を
与えた場合でも正しく算出します。
ktWorkingTime 関数内では、小数誤差が表れないように『分』に変換した上で整数演算で
処理し、結果を返す段階でシリアル値に変換しています。しかし、ktWorkingTime で得られた
日々の結果には小数誤差が無くても、それを集計(月合計や年合計など)すれば、そこには
再び小数誤差が含まれてきます。ktWorkingTime で返す内容も『分』の整数のままにすれば、
集計したとしても、そこには小数誤差など存在しません。
ただ、このままでは、時間データとして利用できませんので、下記のようにしてシリアル値(または
時刻数値)に変換して下さい(ktWorkingTime を使うセル(列)を非表示で隠して、シリアル値に
変換した結果だけ表示させます)。
「Serial=False 」指定により『分』で結果を得た場合、
(1) シリアル値にするには、[1440]で割ります(表示形式を時刻タイプへ)。
(2) 時給計算などを計算する為に時間を【数値(7:45→7.75)】で得たい場合は、
[60]で割ります(表示形式は標準または数値)。
日付跨り(15:00→8:00など)の対処例
下記サンプルの条件
・勤務時間は15分単位で丸め
・日曜/祝日が休日
・日付跨り勤務は、通常勤務の始まる9時以降を翌日分として扱い、
9時で一旦終業とし、9時以降は翌日の勤務として新たに起票する。
このように運用できると、本日の勤務時間を求めるのに、前日から本日へ
跨って勤務(前日起票)の翌日分を足し込まなくても済みます。
(A) (B) (C) ( 5) 時間帯 開始時刻 終了時刻 ( 6) 深夜(朝) 0:00 5:00 ( 7) 残業(朝) 5:00 9:00 ( 8) 午前 9:00 12:00 ( 9) 昼休み 12:00 13:00 (10) 午後 13:00 17:30 (11) 残業(夜) 17:30 22:00 (12) 深夜(夜) 22:00 24:00
(A) (B) (C) (D) (E) (F) (15) 日 付 曜日 始業時刻 終業時刻 始業(丸め) 終業(丸め) (16) 7月27日 (金) 9:00 23:12 9:00 23:00 (17) 7月28日 (土) 16:12 9:00 16:15 9:00 ←(33:00の意) (18) 7月29日 (日) 9:00 15:40 9:00 15:30 ←(9時で切分け) (19) 7月30日 (月) 9:03 19:20 9:15 19:15
(G) (H) (I) (J) (15) 通 常 残 業 深 夜 休 日 (16) 7:30 4:30 1:00 0:00 (17) 1:15 8:30 7:00 0:00 (18) 0:00 0:00 0:00 5:30 (19) 7:15 1:45 0:00 0:00
E16 =IF(C16="", "", ktBoundaryTime(C16,15,"U")) 15分単位で切り上げ F16 =IF(D16="", "", ktBoundaryTime(D16,15,"D")) 15分単位で切り捨て G16 =IF(OR(E16="", F16=""), "", IF(kt営業日判定("10000001000@", A16),
ktWorkingTime(E16, F16, $C$5:$C$11, 3, "0010100#0000000"), 0))H16 =IF(OR(E16="", F16=""), "", IF(kt営業日判定("10000001000@", A16),
ktWorkingTime(E16, F16, $C$5:$C$11, 3, "0100010#0100000"), 0))I16 =IF(OR(E16="", F16=""), "",
ktWorkingTime(E16, F16, $C$5:$C$11, 3, "1000001#1000000"))J16 =IF(OR(E16="", F16=""), "", IF(kt営業日判定("10000001000@", A16), 0,
ktWorkingTime(E16, F16, $C$5:$C$11, 3, "0110110#0100000")))
※ kt営業日判定("10000001000@",A16)‥‥‥日曜/祝日以外 ⇒ True , 日曜/祝日 ⇒ False