iWorkNumbersTimecard

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

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

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


方法は二つありますよね

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

 

Screencapture00220724_101851jst

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

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

 

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

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


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


Website_image20130119_220001_2


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

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


| | コメント (0)

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

今までの経過は
その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow
を見てください。
iworkの関数一覧はこちら[LINK]OpenNewWindow

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


 

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

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

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

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


 

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

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

が必須
 

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

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

が必要です。

今回は

休日は『日曜日』
計算上の日は
『始業時間』24:00=0:00
としています。

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

 

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

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

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

 

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

参考にしてください。


 

【まとめ】

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

 

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

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

 

TIMEVALUEの値を求める。

683x208_numbers_01

24:00以降の対応には不可欠です

時間部 と 分部 を分割

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

を取得します。


23時より大きい数字24時とか26時とかの場合は
以下のように
688x79_numbers_07

TIMEVALUEに1を足して24時以降の時間帯に対応しています。

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


36-24のTIMEVALUE("12:00")+1

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

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

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

ですね。

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

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

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

551x491_numbers_05

 

タイムカードの計算式の場合は
『深夜早朝』

『翌日早朝』

加味する計算式の場合はかなり複雑になります。

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

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

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


 

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

参考にしてください。


 
   

| | コメント (0)

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

今までの経過は
その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow
を見てください。
iworkの関数一覧はこちら[LINK]OpenNewWindow
を見てください。

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


 

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

24:00

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

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


 

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

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

 
まずは
時間を



に分

HH:MM

で時間を表示しますので
497x285_timecard_10_2

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

9:00

入力しても
09:00

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

 

=LEFT(F11,2)


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

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

=VALUE()で包んで

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

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

425x91_timecard_06


 

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

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

=RIGHT(F11,2)

HH:MMの時間文字列を

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

=VALUE()で包んで

数値としておきます。

405x99_timecard_07

 

この=VALUE()の処理は
入力される時間が
23:59までなら必要無い処理です。

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

しか〜し

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

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

1月1日の26:00時は 1月2日の2時
としてしか表示出来ない
そうなんです
Numbersでは
24:00は『翌日』
なのです。

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

 
脱線しましたが

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

HHとMM
に分割しました。

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

HH:MM

単純にTIMEVALUEに戻すなら
TIMEVALUE(HH&":"&MM)

十分なわけですが

24時以降の場合は

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

このように
24:00のTIMEVALUE値である『』を
例だと
36-24=の12のTIMEVALUEに足す

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

636x102_timecard_08

 

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

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

454x152_screen_01


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

■それぞれの合計

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

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

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

| | コメント (0)

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

今までの経過は
今までの経過は
その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow
を見てください。
iworkの関数一覧はこちら[LINK]OpenNewWindow

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

 

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

454x152_screen_01

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

時間帯Aと時間帯Bでそれぞれ
『勤務時間』

『深夜時間』
を求めれば良い事になります。

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

 

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

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

先にも書きましたが
Mac版のExcelでは
=TIMEVALUE("24:00")=0

0(ゼロ)を返します。
でも
Numbersでは
=TIMEVALUE("24:00")=エラー
になってしまいます。
Screencapt
これが最も困ります。
エラーになるので『エラーの制御』をしなくてはなりません。

497x161_screen_02

数字は『1バイト半角』入力にしても
区切り文字としての

コロンの入力について
『2バイト全角の:』と『1バイト半角の:』
を考慮に入れるだけでも
エラー制御が2重に必要になってしまいます。

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

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

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

 

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

728x314_screen_03_2

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

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

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


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

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

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


| | コメント (0)

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

今までの経過は
今までの経過は
その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow
を見てください。
iworkの関数一覧はこちら[LINK]OpenNewWindow

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

Numbersは09です。

 

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

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

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

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

 

=TIMEVALUE("1:00")

0.0416666666666667

=TIMEVALUE("0:15")

0.0104166666666667

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

 
そこで
値を1時間の値割ります。
=TIMEVALUE("0:15")/TIMEVALUE("1:00")

する事で
0.25= 15分は0.25時間
を求める事が出来るわけです。

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

 

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

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

 

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

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

1005x333_timecard_01[LINK]OpenNewWindow

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

 

その他のポイント『COUNTIF』

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

出勤日数をカウントしています。
452x126_timecard_04


 
その他のポイント『MAX』

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

=J44/MAX(C11:C41)
435x104_timecard_05_3

 

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

 

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

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

参考にしてください。

| | コメント (0)

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

今までの経過は
その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow
を見てください。
iworkの関数一覧はこちら[LINK]OpenNewWindow

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


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

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

 
572x322_numbers_03

 

■残業計算
労働時間が8時間超なら 8時間を超えた分を計算
=IF(労働時間>8,(労働時間-8),"")

計算になります。

■深夜
24:00を1に置き換えるので
そこがややこしいですが
=IF(終業時間>22:00,(終業時間-22:00),"")
なんですが
それぞれの『時間』はTIMEVALUEで求める所がさらにややこしい

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


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


 

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

649x223_numbers_01

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

 


640x325_numbers_02


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

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

にしています。

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

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

注意したいものです。

 

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

他の事項は
その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow

参考にしてください


 

| | コメント (0)

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

その1[LINK]OpenNewWindow
その2[LINK]OpenNewWindow
その3[LINK]OpenNewWindow
その4[LINK]OpenNewWindow
その4+[LINK]OpenNewWindow
最終回[LINK]OpenNewWindow
を見てください。
iworkの関数一覧はこちら[LINK]OpenNewWindow

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

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

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

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

528x404_numbers_03

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

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

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

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

【3】引算の結果からさらに引算する

エラーになってしまいます。

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

 

596x624_numbers_01

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


細かい計算でエラーが出るようなので
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関数を足して小数点以下2位で四捨五入します。
【1】で得た1時間のTIMEVALUEの値が割り切れない数値なので
四捨五入して値を整形します。

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

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

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


 

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

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

『0.0104166666666667』

で割っています

これは

『TIMEVALUE("0:15")』

って事です。

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

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

TIMEVALUE("0:10")

の戻り値である

0.00694444444444444

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


 

420x294_numbers_01


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

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

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

| | コメント (0)

その他のカテゴリー

Accessibility AccessibilityCheck AccessibilityForm AccessibilityInDesign AccessibilityPDF Acrobat Acrobat Action Acrobat Annotation Acrobat AppleScript Acrobat Layer Acrobat Plug-ins Acrobat Portfolios Acrobat Print AcrobatBarcode AcrobatDialog AcrobatForm AcrobatJS AcrobatMenu AcrobatPDF AcrobatStamp AcrobatYouTube AddressBook Adobe Adobe InDesign AdobeAppleScript AdobeBridge AdobeIllustrator AdobeJSX aed Alfresco Android AnimationGif Apple AppleScript AppleScriptBasics AppleScriptCharacter AppleScriptColor AppleScriptDroplet AppleScriptErrorNum AppleScriptFolder AppleScriptFontBook AppleScriptRename AppleScriptTools AppleSymbols Applications Barcode Barcode2D BarcodePostal BetterHTMLExport Book Browser buzz Certificates CharacterEntity CharacterSets Colors Cool Site CSS Cutting DecoMail DecorationMail Design Desktop Diff DJ dmg DNS Documents Download eBook Editer eMail Envelopes ExifTool Facebook FFmpeg File System Fonts FontsTool FontsWeb FOOD FormPrint ftp Gadget Gif Animation Google HexEditor HTML info iPhoto ISBN ISO iTunes iWork iWorkNumbers iWorkNumbersCalendar iWorkNumbersTimecard iWorkPages JavaScript JeditX JeditX Regexp JeditXAppleScript JIS jquery Letterpress Library logo Mac Admin Mac Archiver Mac Browser Mac Browser Plugin Mac QuickLook Mac Spotlight Mac Video Map Mobby Moto Movies Music Network Basic ntp OCR Office OSX Pantone Paper PDFlib Permission Photo Pictograms Print Public QuickLook QuickTime QuickTimeSetting QuickTimeSound Real Media ReName ResourceFork ruby Screen ScreenCast Search Security SEO Sharing SLAResource Spotlight Stamp SWF Tutorial PSD TV Twitter Typography Unicode Utilities Video WebFont Wedding Windows WindowsMedia XML XMP XPS YouTube YouTube Rss