【Excel】時間計算について相談(勤怠管理)

これから始めようとしているところで恐縮ですが、最初が肝心だと思って質問させてください。

タイムカードの打刻データから、勤怠管理をしなければいけません。
打刻データはCSVで、
スタッフID、日付、開始時刻、終了時刻、休憩時間程度しかありません。
時間は、830、2325のように表現されています。
(8時30分、23時25分が数字3桁または4桁で表されている)

ここからチェックすべき内容が沢山あり、
毎月の残業時間が80hを超えてないか、とか、45h超えが年内に何か月あったか、とか、
36協定が守られているか、とか・・・
労働時間、(法定)時間外労働、法内残業、土日祝も鑑みながら・・・
※賃金の計算は行いません。

これらの時間集計を行うにあたり、最初の値の持ち方についてアドバイスください。
全部、(分)に置き換えるべきとか、「8:30、23:25」に変換してから始めるとか。

質問者からの補足コメント

• 

  タイムカードと言っていますが、WEB打刻システムでクラウド上に保存されています。
  このシステムは既に何年も稼働中で交換はしません。
  ここからエクスポートされたCSVの時間計算しやすいようにどう加工したら良いでしょうか？というのが質問になります。

    補足日時：2021/03/04 23:51

No.8 回答者： stomachman 回答日時： 2021/03/05 22:05

No.7へのコメントについてです。

> 48:00＞45:00」という条件式

ってのがどういうことだかさっぱりわからんですが、残業を時間単位で集計したのなら、あるセル（例えばC4)に48と算出された時間（単位は1時間）を使って
　=C4>45
とやることになるんじゃ？
　C4の値を（なぜだかわからんけれど）どうしても":"で区切った形で表示したければ、（Excelでは時間の値は1日が単位ですから）45時間なら セルD4に =C4/24 とやるか、あるいは =TIME(C4,0,0)とやって、D4を書式「時刻」のフォーマットで表示させれば表示される。

No.7 回答者： stomachman 回答日時： 2021/03/05 14:21

>最初の値の持ち方

　「2020/2/28 17:45 から2020/3/1 8:30までは何分間か？」という計算をやろうとしたら、時刻だけじゃなく、年月日の情報が不可欠ですよね。だから、日付と時刻は切り離して考えてはいけない。絶対的な時点を表す情報（”2021/3/04 12:34”のような）に一旦変換しておくのが適切でしょう。いずれ月単位で合計勤務時間を計算しなきゃならんのですし、曜日や休日も考慮しなくちゃいけない。

　例えば、日付のデータがセルA1に“20210304”という形式で、また時刻のデータがセルB1に”1234”という形式で与えられたとして、セルC1に
=DATE(A1/10000, MOD(A1/100,100), MOD(A1,100)) + TIME(B1/100,MOD(B1,100),0)
とやれば、セルC1には”2021/3/04 12:34”と出るでしょう。この結果は実際のところは実数値であり、だから足し算引き算ができます。
　この実数値の意味は、システムで決まってる起点(1900/1/1 0:00:00とか)からの日数です。（時刻は小数点以下の部分で表されています。つまり1時間= 1/24日、1分=1/1440日、と考える訳です。）で、セルC1の表示フォーマットを日付用のものに設定することによって、この実数値が例えば”2021/3/04 12:34”という形式で表示される、という仕掛けです。

　日付と時刻の情報を一度このように整理すれば、あとでそこから日付だけ、時刻だけ、何曜日か、何月か、その月には何日あるか、などなどの情報を取り出すには、いろんな関数が用意されています。

この回答へのお礼

ありがとうございます。
確かに日付と一緒に持てば色々出来そうですが、
例えば具体的に気になった点は、
残業時間3hの日が複数あって、1か月の残業時間の累積が48hになった時、
「48:00＞45:00」という条件式で検知できますでしょうか？
簡易的に試したところ「False」となってしまったので、
これは分単位の整数に変換して「2880＞2700」みたいに検知させないといけないのでは？と思い始めました。

あと、「2020/2/28 17:45 から2020/3/1 8:30までは何分間か？」よりも
「17:45から32:30までは何分間か？」という計算の方が良いのかな？と思ったりもしましたが素人ですみません。。。

お礼日時：2021/03/05 18:04

No.6 回答者： yucco_chan 回答日時： 2021/03/05 12:46

>例えば、開始8:30、終了22:15だったりして、

休憩時間1時間とすると、1日の労働時間をどのように計算できますか？

うーん。
Ｅｘｃｅｌ は置いてき、手計算ではどのように計算しますか？
その計算方法を、Ｅｘｃｅｌ の計算式に置き換えるだけなのですが。
で、手計算を置き換えたＥｘｃｅｌの計算式よりも、Ｅｘｃｅｌならではの
テクニックを使った計算式があるかもしれませんが、
質問するなら、手計算を置き替えた計算式を示して、
アドバイスを求めるべきです。

それと、例えばではありますが、終了22:15 なのに
休息時間は1時間しか無いのですか？
以前に私が勤めていた会社からは労働基準法違反なると
説明を受けていたのですが、検索しても今は違法にはならないようです。
法律が改悪された？のでしょうかね。検索の仕方が悪いだけかしれませんが。
３６協定などでスレ主さんの会社ではどのようになっていますか？
↑３６協定などをしっかり把握していないと目的が達成できないとの危惧です。

No.5 回答者： mike_g 回答日時： 2021/03/05 01:06

[No.4お礼]へのコメント、

あの～、その～、其れ、お門違ひの御尋ねかと。
貴方が労働基準監督署にでも赴いて調べた結果を教えて下さいナっ！

No.4 回答者： mike_g 回答日時： 2021/03/04 21:18

ご參考までに。

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

この回答へのお礼

ありがとうございます。
ただ、その加工をしてしまってから、法定外残業だとか、
今月残業時間が規定を超過したとか計算する時に支障ないでしょうか？

お礼日時：2021/03/04 23:56

No.3 回答者： yucco_chan 回答日時： 2021/03/04 21:13

>数字3桁または4桁で表されている

どちらでも良いですね。
強いて言えば、時と分のセルを分けた方が楽です。

この手の管理表を作成する時に厄介なのは、
休息時間と休日ですね。

例えば、開始時刻が829（８：２９）の場合は、30分までの1分は
どのように扱うのかとか、遅刻/早退したときの扱いとか、、
祝日は休日になると思いますが、計算時にどうやって休日にするのか。

この回答へのお礼

打刻システムの入力が5分単位です。
まずアドバイス頂きたいのが時刻計算しやすい加工方法についてです。
時と分のセルを分けると後々やり易いでしょうか？

例えば、開始8:30、終了22:15だったりして、
休憩時間1時間とすると、1日の労働時間をどのように計算できますか？

お礼日時：2021/03/04 23:54

No.2 回答者： rukuku 回答日時： 2021/03/04 21:07

こんばんは

Excelの日付入力は、便利な反面、余計なことをする場合もあります。
例えば、今日「1-4」と入力すると、「2021/1/4」として扱われてしまいます。

タイムレコーダーをExcelにデータ出力できるものに交換するのが一番簡単な方法です。

そうは言っても、それだけの予算が確保できない場合もあります。
代案としては、タイムレコーダーのCSVデータをExcelが正しく認識できる形に変換する必要があります。

1.関数を駆使する
2.VBAを使う
という方法が思い浮かびますが、どちらも大変です。
…いづれも、Excelが導入されているパソコンであれば、「人件費」以外のコストはかかりません。

「経験者に質問した」という前提で上司さんと交渉してみてください。

この回答へのお礼

そうですよね。Excelの余計な扱われ方で計算が狂わないように確実な手法を決めておきたいです。
勤怠システムは既に決まったものを使っています。
そこから出力できるCSVからの加工についての相談です。
どんな方法がおすすめですか？

お礼日時：2021/03/09 10:36

No.1 回答者： ShowMeHow 回答日時： 2021/03/04 19:09

元データは、なるべくそのままで取り込みましょう。

その後、加工をしていくほうが、デバッグしやすいです。

この回答へのお礼

システムからCSV出力される形が、3桁か4桁の数値です。
そこから時間計算するためにどんな加工を加えれば後々万能に計算できるでしょうか？

お礼日時：2021/03/04 23:55