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 | AcrobatX | AcrobatYouTube | AddressBook | Adobe | Adobe InDesign | AdobeAppleScript | AdobeBridge | AdobeIllustrator | AdobeJSX | aed | Alfresco | Android | AnimationGif | Apple | AppleScript | AppleScriptBasics | AppleScriptCharacter | AppleScriptColor | AppleScriptDroplet | AppleScriptErrorNum | AppleScriptFolder | AppleScriptRename | AppleScriptTools | AppleSymbols | Applications | Barcode | Barcode2D | BarcodePostal | BetterHTMLExport | Book | Browser | buzz | CAD | Certificates | CharacterEntity | CharacterSets | Chrome | Colors | Cool Site | CriminalMind | CSS | Cutting | DecoMail | DecorationMail | Design | Desktop | Diff | DJ | dmg | DNS | Documents | Download | DTP | eBook | Editer | eMail | Envelopes | ExifTool | Facebook | FFmpeg | File System | Fonts | FontsTool | FontsWeb | FOOD | FormPrint | ftp | Gadget | Gif Animation | glee | Google | Graphic | HexEditor | HTML | info | iPhoto | ISBN | ISO | iTunes | iWork | iWorkNumbers | iWorkNumbersCalendar | iWorkNumbersTimecard | iWorkPages | JavaScript | JeditX | JeditX Regexp | JeditXAppleScript | JIS | jquery | Kanji | Kids | 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 | OCR Font | Office | OfficePowerPoint | OSX | Pantone | Paper | PDFlib | Permission | Photo | Pictograms | Pictures | Press | Print | Printing | Public | QuickLook | QuickTime | QuickTimeSetting | QuickTimeSound | Real Media | ReName | ResourceFork | ruby | Sample | Screen | ScreenCast | Search | Security | SEO | Sharing | Sign | SLAResource | Sound | Souvenir | Spotlight | Stamp | SWF | Tutorial PSD | TV | Twitter | Typography | Unicode | Utilities | Video | WebClip | WebFont | Wedding | Windows | WindowsMedia | XML | XMP | XPS | YouTube | YouTube Rss