当サイトは第三者配信の広告サービス(アフィリエイト広告、アドセンス広告)を利用しています。

関数3

アイキャッチ|ぽつぽつLife

仕事で時間計算をする事は多いのではないでしょうか?

その時に活躍するのがTIME関数

TIME(時, 分, 秒)

 返される少数は 0.00~0.99988426 の範囲

 返される時刻は 0:00:0 (午前 00 時) ~ 23:59:59 (午後 11 時 59 分 59 秒) 

 

①TIME(0,0,0) → 12:00:00AM → 0:00:00  → 0.00

 0=午前12時00分00秒が基準となります

②TIME(1,0,0) →  1:00:00AM →  1:00:00  → 0.041666667

③TIME(0,0,1) →  12:00:01AM → 0:00:01  → 1.15741E-05

例えば

④TIME(0,0,15000) →  4:10:00AM → 4:10:00  → 0.173611111

15000秒を60で割ると250分

※秒は0 から 32767 を返し、59以上を時間、分に変換されます

60で割ると4.1666666時間

※分は0 から 32767 を返し、59以上を時間、時間に変換されます

時刻表示で 4時間10分となる

※時間は0 から 32767 を返し、23より大きいい値は

 24 で除算され、剰余は時間値として処理されます

現在は数字を直接入力していますが

タイムカードからのデータ化が「秒」でしか表示されていないものなど

それぞれの会社でシステムの特徴があるかと思います。

それを、時刻表示に変えたり、タイムカードからの給与計算の時などに

使えるかと思います。


例えば、上記のような表で労働時間を計算する時は

休憩時間が分表示なので・・・

ROUND(MIN($L2-$K2-TIME(0,$M2,0),”8:00″)*24,2)

分をTIME関数を使って時間計算に組み合わせています。

※24を掛けているのは、給与計算ができるように時刻を数値に変換しています

※MIN関数を使っているのは所定労働時間は8時間でそれを超える時間を

 残業時間とするため

前回の引き続き、TIME関数も使って勤怠計算をします。

【所定労働時間計算】

TIME関数は休憩時間の「分」を時間計算

にするために使用しました。

退勤時間 - 出勤時間 - 休憩時間 の 答え

と 8時間の どちらか小さい方

=ROUND(MIN($L2-$K2-TIME(0,$M2,0),”8:00″)*24,2)

                        ↓

                     所定時間の上限    

簡単な方法だと、これで良いのでしょうが・・・

夜勤勤務がある場合はこの数式だとマイナスになってしまいます。

6:00 - 22:00 - 1:00 = -17:00

夜勤勤務がある場合はこの数式だとマイナスになります。

引き続き、TIME関数を使って勤怠計算をします。

【深夜時間計算】

TIME関数は休憩時間の「分」を時間計算

にするために使用しました。

労働基準法37条》

原則午後10時から午前5時までの間に労働させた場合は、通常の労働時間の賃金の計算額2割5分以上の率で計算した割増賃金を支払わなければなりません。

           —–労働局のHPより—–

とあることから、深夜時間を算出しなければなりません。

そこで、こんな式にしてみました。

=(IF(K4>L4,

(MIN(L4+”24:00″,”29:00″)-MAX(“22:00”,K4)-TIME(0,M4,0))*24,

IF(“22:00”>K4,(L4-K4)*24,

0)))

もし

退勤時間が出勤時間より大きい?

YES

退勤時間に24時を足した時間と午前5時の小さな方

引く

出勤時間と午後10時の大きな方

NO

もし

出勤時間が午後10時より大きい?

YES

退勤時間

引く

出勤時間

NO

0

 

退勤時間が出勤時間より小さいという事は

日をまたがっているということなので

最初のIF文で日をまたがったら24時を足して間の時間を算出するようにします。

その時に、深夜時間は午前5時までなので

午前5時を超える時間を無視するため

MIN関数を使って、低い値をピックアップします。

逆に、

出勤時間は午後22時以前の時間は関係ないので

MAX関数で22時以前の時間を省くようにします。

そして、それぞれ算出した時間を引いて深夜時間を算出します。

出勤時間が22:00 退勤時間が24:00 など

出勤時間と退勤時間が逆転しない場合も

想定されるので、更にIF関数を用いて

出勤時間が22:00を超えている場合のみ

時間計算をするようにさせました。

《本日の関数》

TIME関数

IF関数

MIN関数

MAX関数

アイキャッチ|ぽつぽつLife

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
目次