【Numbers】時間の計算 タイムカード 【15分単位】

タイムカードの給料計算で
良くあるのが
『１５分単位』

働いた分を正しく受給できないので
問題になると言えば問題になるのではないか？と思いますが
この方式をとっている所は多いのではないでしょうか？

方法は二つありますよね

◆入力時に15分単位に整形する方法と
◆入力はタイムカードの打刻通りで計算

24時以降に対応する場合に有効な
一旦時と分をわけて計算する場合は
このように

『分』をVLOOKUPで整形してしまうのが
手っ取り早いです。

一部計算式変更して
３６時以上に対応しました。

「timecard_v4a.numbers.zip」をダウンロード[Download]

でも
一般的にはif分で分岐計算するほうが一般的ですね
ポイントは

始業時の４５分以降の分帯を次の時間へ繰り上げる処理です。

v「timecard_v4a2.numbers.zip」をダウンロード

【Numbers】時間の計算 タイムカード 【最終回まとめ】

今までの経過は
その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
を見てください。
iworkの関数一覧はこちら[LINK]

１５分単位で集計する場合のヒントはこちら[LINK]

いよいよiWorkのNumbersで作るタイムカードの最終回です。
長かったぁ

結論先に言うとエクセルでやった方が良いです...笑

Numbersで時間計算する時のポイントは
今までさんざん言って来た通りで
『24：00』や『26：00』等
日付をマタグ時間計算が（そのままでは）使えない点でした。

色々やってきましたが
前回[LINK]使った
日付を文字列として分解してから
『TIMEVALUEの値として一旦格納して計算する』
方式が一番スマート（結果として）なのかなぁ？
他に良い方法を思いつかないので
これで終了にしちゃいます..w

タイムカードの計算なので
考慮する点は

■0：00〜5：00までの早朝勤務
■休憩時間
■22：00〜翌２４：00までの深夜勤務
■８時間以上労働時の残業手当

が必須
 

決まり事として必要なのは

◆休日勤務日
◆計算上の日の変わり目

が必要です。

今回は

休日は『日曜日』
計算上の日は
『始業時間』２４：００＝０：００
としています。

 
業務先によっては『朝７時』とか
ツタヤのレンタルのように朝１０：００
とかを日付の変更位置にしている所がありますね。

表計算でのタイムカードの計算は
汎用性をもたせよう！とすると
結構式が長くなり難しです。

ある程度自分の環境や労働条件に
あわせて作りなおすのが吉ですね。

基本9時〜５時で昼に１時間休憩
のような
労働条件なら
早朝等を考慮に入れなくても良いですしね。

では
ファイルはこちら
「timecard_v3d.numbers.zip」をダウンロード[Download]

参考にしてください。

【まとめ】

■ numbersでの時間計算の基本は『TIMEVALUE』
■２４時以降の計算が必要な場合は複雑になります。
■ある程度セルフォーマットで制御

「timecard_v4.numbers.zip」をダウンロード

このファイルを元に説明します。

TIMEVALUEの値を求める。

２４：００以降の対応には不可欠です

時間部　と　分部　を分割

=IFERROR(VALUE(LEFT(F11,2)),"")で時
=IFERROR(VALUE(RIGHT(F11,2)),"")分

を取得します。

２３時より大きい数字２４時とか２６時とかの場合は
以下のように

TIMEVALUEに１を足して２４時以降の時間帯に対応しています。

,IF(U11>23,SUM(1,TIMEVALUE((36-24)&":"&V11))

３６-２４のTIMEVALUE（"12:00")+1

で３６時のTIMEVALUE値を求めているわけです。

 
深夜早朝部は
早朝は
IF(XXXX<TIMEVALUE("5:00")

深夜は
IF(XXXX>TIMEVALUE("22:00")
翌日早朝は深夜の条件に
IF(XXXX<SUM(1,TIMEVALUE("5:00")

ですね。

 
時間入力の所には
以下のようにフォーマットを設定する事で
『文字列』が入力出来なくなり
必ず『時間』（値）が入力されます。

こうする事で必ず『１バイト半角文字』が
入力されます。
その後の計算で一応『VALUE』を使って整形しています。

 
分を必ず２桁で表示する
フォーマットも一応設定しています。
（設定しなくても数式の答えは同じですけどね）

タイムカードの計算式の場合は
『深夜早朝』
と
『翌日早朝』
を
加味する計算式の場合はかなり複雑になります。

通常の計算ではエクセルとさほど違いはありませんね。

参考になりましたでしょうか？

でもまぁ
エクセルでやった方が良いかなぁ〜と思いますね。

では
ファイルはこちら
「timecard_v3d.numbers.zip」をダウンロード[Download]

参考にしてください。

【Numbers】時間の計算 タイムカード 【その4+】

今までの経過は
その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
を見てください。
iworkの関数一覧はこちら[LINK]
を見てください。

１５分単位で集計する場合のヒントはこちら[LINK]

ここ数回で問題になっているのは

２４：００

を超える時間の指定についてです。

今回は『文字列を操作〜分割』
する事で
２４時以降の時間にも対応する事を考えてみます。

まずは
作りかけの途中のファイルを見てください。

「timecard_v3a.numbers.zip」をダウンロード

 
まずは
時間を
時
と
分
に分

HH：MM

で時間を表示しますので

カスタムフォーマットを使って

９：００
と
入力しても
０９：００

とゼロサプレス[LINK]表示するようにしておきます。

=LEFT(F11,2)

で
時間を
『左から』『２文字』取得します。

このままだと
『半角１バイト』と『全角２バイト』
の判定がややこしいので

＝VALUE（）で包んで

文字列→数値　をしておきます。

前回等でも問題になって『：』の区切り文字は『VALUE』で変換出来ないので
この方法は使えませんでした。

続いて『分』側の整形にうつります。

先の『時』部分と同じ発想で

=RIGHT(F11,2)
で
HH：MMの時間文字列を

『右から』『２文字』を取得して

＝VALUE（）で包んで

数値としておきます。

この＝VALUE（）の処理は
入力される時間が
２３：５９までなら必要無い処理です。

セルのカスタムフォーマットで『時間』指定しているので
２３：５９までの入力は
『かな』で入力しても『時間』に自動的に変換されます。

しか〜し

カレンダーの回で[LINK]書いたように

Numbersは
『必ず日付時刻形式』で時間を持つ
時間には必ず日付が必要
なので
２６：００は翌日なので
日付が変わるわけです
同日とは処理出来ない。

１月１日の26:00時は　１月２日の２時
としてしか表示出来ない
そうなんです
Numbersでは
２４：００は『翌日』なのです。

エクセルでは先に書いたように
日付や時間は
『日付連番』+『TIMEVALUE値』です。
エクセルでは３６時等のTIMEVALUE値がありますので
このような事にならないわけです。

 
脱線しましたが

HH：MM形式の時間の入力を

HHとMM
に分割しました。

では
この分割されたHHとMMを元に
TIMEVALUEの値にすれば良いわけです。

HH：MM

単純にTIMEVALUEに戻すなら
TIMEVALUE(HH&":"&MM)
で
十分なわけですが

２４時以降の場合は

SUM(1,TIMEVALUE((HH-24)&":"&MM)）

このように
２４：００のTIMEVALUE値である『１』を
例だと
36-24＝の１２のTIMEVALUEに足す

事で
３６時のTIMEVALUE値『1.5』を求める事が出来るわけです。

これで
■深夜勤務時間中の休憩に対応する
■２４時を超える勤務時間の集計に対応する
■残業時間に対応する

タイムカード計算
が出来そうですね。

■【時間帯A】労働時間
■【時間帯A】深夜時間
■【時間帯B】労働時間
■【時間帯B】深夜時間

■それぞれの合計

■労働時間が８hを上回った分を残業時間

を計算すれば良いですね。

続く　（続かないかもしれない...w）

【Numbers】時間の計算 タイムカード 【その４】

今までの経過は
今までの経過は
その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
を見てください。
iworkの関数一覧はこちら[LINK]

１５分単位で集計する場合のヒントはこちら[LINK]

今回は深夜時間帯での『休憩』に対応します。

考え方は↑のように
時間帯を２つに分けて考えれば良いですね。

時間帯Aと時間帯Bでそれぞれ
『勤務時間』
と
『深夜時間』
を求めれば良い事になります。

そう逆の発想ですね。
『休憩時間を求めない』にしたわけです。

ポイントって言うか、なぜこんなに時間計算が変な事になるんでしょ。
Excelに目を向けてみても
Macintosh 版 Excel と Windows 版 Excel では、既定の日付システムが異なります。

Macintosh版でもExcelとNumbersでは関数に互換が無いものがあります。

先にも書きましたが
Mac版のExcelでは
＝TIMEVALUE（"24:00")=0
で
0（ゼロ）を返します。
でも
Numbersでは
＝TIMEVALUE（"24:00")=エラー
になってしまいます。

これが最も困ります。
エラーになるので『エラーの制御』をしなくてはなりません。

数字は『１バイト半角』入力にしても
区切り文字としての
『：』
コロンの入力について
『２バイト全角の：』と『１バイト半角の:』
を考慮に入れるだけでも
エラー制御が２重に必要になってしまいます。

この『文字列』の操作は次回やるとして
今回は前回同様

IF(時間が="24:00"ならTIMEVALUEとしての１,　　//『１バイト半角の:コロン』
IF(時間が="24：00"ならTIMEVALUEとしての１　　//『２バイト全角の：コロン』

の２通りだけの制御をしておきます。

次に考えるのが
どんな『パターン』があるかな？
って事でしょうか？

ざっと考えただけでも
このぐらいの種類の入力パターンが想定されます。
（中にはありえない感じのもありますけど..w）

これらを考慮して計算式を作成していきます。

基本は前回の計算と同じ
(TIMEVALUE(終了時間)-TIMEVALUE(開始時間))/TIMEVALUE("1:00"))

で
時間を数字として定義できますね。

 
今回のサンプルファイルはこちらです。
numbersデータをダウンロード[Download]
参考にしてください。
（結構数式が横に長いので難解かもしれません....汗）
 

続く　(続かないかもしれない...w）

【Numbers】時間の計算 タイムカード 【その３】

今までの経過は
今までの経過は
その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
を見てください。
iworkの関数一覧はこちら[LINK]

１５分単位で集計する場合のヒントはこちら[LINK]

Numbersは09です。

Numbersでもエクセルでも
タイムカード式の時間の計算のポイントは

1:00=1＝１時間
0:30=0.5=30分

で最後に集計したい。
ところですよね。

そこで『TIMEVALUE』関数を使って計算するわけです。

=TIMEVALUE("1:00")
は
0.0416666666666667

=TIMEVALUE("0：15")
は
0.0104166666666667

とする事でTIMEVALUEの値が求められます

 
そこで
値を１時間の値割ります。
=TIMEVALUE("0：15")/TIMEVALUE("1:00")
と
する事で
0.25＝　15分は0.25時間
を求める事が出来るわけです。

これがタイムカード式に時間を計算する時のポイントになります。

先にも書きましたが
TIMEVALUE関数の問題点は
=TIMEVALUE("24：00")　がエラーになる事です。

=TIMEVALUE("24：00")　=1
=TIMEVALUE("36：00") =1.5
の値を返すようになってくれれば
どんだけ
計算が楽に出来る事か...汗　改善してほしいなぁ...汗

で出来上がったのが
日にちをまたぐ勤務に対応した物です。
やれば出来るのですが
計算式が異常に長くなってしまうので
入力部を２つにわける事で対応しました。

これを参考に作り込んでみてください。

[LINK]

 
ファイルはこちら
「timecard.numbers.zip」をダウンロード[Download]

その他のポイント『COUNTIF』

『出勤日数を数える』
『○』の数を数えるで計算しています。
=COUNTIF(E11:E41,"○”)
で
出勤日数をカウントしています。

 
その他のポイント『MAX』

万年カレンダーの日にちの最大値を求める事で
『その月は何日まであるのか？』を求めています。

=J44/MAX(C11:C41)

その他のポイント『COUNT』
残業日数を求める所で使っています。
COUNTで整数のあるセルの数を数えています。
それ以外の所には""が入っているからです。
=COUNT(O11:O41)

実はこのタイムカード式は
『休憩を深夜時間に取った』場合
に対応していません。

 
急いでザッと作ったので
計算式に誤りがあるかもしれませんので
参考にしてもらって
ご自分で作ってみてください。

参考にしてください。

【Numbers】時間の計算 タイムカード式【その２】

今までの経過は
その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
を見てください。
iworkの関数一覧はこちら[LINK]

１５分単位で集計する場合のヒントはこちら[LINK]

Numbersのカレンダー[LINK]のつづきです。

前回Numbersでの時間計算の基本を調べましたので
今回は応用って感じでしょうか

 

■残業計算
労働時間が8時間超なら　8時間を超えた分を計算
＝IF(労働時間＞８,（労働時間-8）,"")
な
計算になります。

■深夜
24：00を１に置き換えるので
そこがややこしいですが
＝IF（終業時間＞22：00,（終業時間-22：00）,""）
なんですが
それぞれの『時間』はTIMEVALUEで求める所がさらにややこしい

ここでエラー回避のトリック
とにかく24:00をTIMEVALUEが認識しないので
24：00（：が全角２バイド）
でも
24:00（:が半角１バイト）
でも
１を返すようにIF分を２重にしておく

■休日
曜日欄で判断させています。
＝IF（曜日＝"日曜日",労働時間,"")
で良いでしょう

今回は『タイムカード』を目指していますので
タイムカードらしい事もします。

■平均労働時間が８時間を超えたら『赤』表示する。

■合計残業時間が６０時間を超えたら『赤表示』

■さらに８０時間を超えたら『赤文字』表示

にしています。

何事も『過ぎる』といけませんからね

働き『過ぎ』
飲み『過ぎ』
食べ『過ぎ』

注意したいものです。

今回使ったNumbersファイルはこちら
「calendar3.zip」をダウンロード

他の事項は
その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
も
参考にしてください

【Numbers】時間の計算 タイムカード式【その１】

その１[LINK]
その２[LINK]
その３[LINK]
その４[LINK]
その４+[LINK]
最終回[LINK]
を見てください。
iworkの関数一覧はこちら[LINK]

１５分単位で集計する場合のヒントはこちら[LINK]

Numbersのカレンダー[LINK]の続きです。

タイムカード形式の時間の計算をしてみましょう。

Numbersのバージョンは09です。
 

「TIME.zip」をダウンロード

上の画像を見てください。
Numbersの時間の計算にはちょっとクセがあって
エクセルユーザーにはかなり違和感があるかもしれません。

【1】引算
=C8-C7
って形式で特に何も考えずに
セルの書式設定も一緒に変更されます。これは問題ないですね。

【2】足し算
足し算自体は出来てるのですが
上の引算の時とはセルの書式が異なっています。

【3】引算の結果からさらに引算する
と
エラーになってしまいます。

タイムカード式の計算を考えていますので
『休憩時間』を引く事がこのままでは出来ない事になります。

「TIMEVALUE.zip」をダウンロード

細かい計算でエラーが出るようなので
TIMEVALUE　関数を利用して計算します。

このTIMEVALUEは
TIMEVALUE 関数は、指定された日付／時刻値またはテキスト文字列の時刻を、24 時間を 1 とする小数で返します。

0：00が0　12：00が0.5　24：00が1
となります。

【1】1時間のTIMEVALUE
1：00のTIMEVALUEは『0.0416666666667』となります。
これは
＝1÷24時間ですね

【2】TIMEVALUE値を時間に変更します。
TIMEVALUEの値を/0.041666666667で時間表示になりますね。

【3】ROUND関数を足して小数点以下２位で四捨五入します。
【1】で得た１時間のTIMEVALUEの値が割り切れない数値なので
四捨五入して値を整形します。

【4】TIMEVALUE値として引算をします。

【5】TIMEVALUE値は24:00に対してエラーを返しますので
24:00だった1にする
IF文を付け加えます。

※TIMEVALUEの値は24:00を許容しませんこれが時間計算をややこしくしています。

タイムカード計算を目的としていますので
『勤務時間は１５分単位を基準』としました。

今回のサンプルでは『15分刻み』で時間計算する
そんな訳ですので

『0.0104166666666667』

で割っています

これは

『TIMEVALUE("0:15")』

って事です。

分で計算するなら
必要無い計算です。

また
１０分刻みで計算する歯場合

TIMEVALUE("0:10")

の戻り値である

0.00694444444444444

を使えば良い事になります。

「calendar2.zip」をダウンロード

numbersのタイムカードらしくなりました。

後は『残業』と『深夜』の計算になります。
続く