自分を活かして 相手を活かして 今を活かす

【GAS】Googleフォームで回答したデータから勤務時間を計算し、[h]:mm形式に変換する

こんにちは、おかちゃんせんせいです!

Googleフォームで回答した内容をもとに勤務時間を集計し、スプレッドシートに出力するフローを構築しようとして、つまずいてしまったことがあったため、備忘も兼ねて記録に残すことにしました。

◆お知らせ◆

【マインドマップ公開講座】

<マインドマップ講座ステージ1:創造編>

《日時》 10月26日(土) 10:00~16:00

《場所》 道場kirari

広島市安佐南区八木8-9-33

《受講料》 33,000円

《参加特典》めちゃくちゃ描きやすいマインドマップのノートとペンがもらえます!

詳しくはこちらから


【まとめ記事】

現在、これまで書いてきた記事をテーマ別にまとめています。

詳しくはこちらから

Googleフォームで回答したデータから勤務時間を計算し、[h]:mm形式に変換する

勤務時間の計算までは上手くいく

まず、下図のようにGoogleフォームで
・開始時刻
・終了時刻
・休憩時間
を作成し、それぞれのタイプを時刻(hh:mm)に設定。

その上で、上記3つを回答した結果から、まずは休憩時間(restTime)をミリ秒として取得。勤務時間を算出します。

GAS
const restTime = 休憩時間 - new Date("1899/12/30 00:00:00");

その上で、勤務時間(workingTime)を下記計算式で算出して、ミリ秒として取得します。

GAS
const workingTime = ((終了時刻 - 開始時刻) - restTime);


上記の内容については、以前つまずいたことがあり記事にまとめてありますので、詳細については下記記事を参考にしてくださいね。

【解決】GASでGoogleフォームから作成したスプレッドシートのデータを活用する際につまずいた2つのポイント

24時間以上を表示させる時分([h]:mm)に変換する方法

次に、ミリ秒のデータを[h]:mm形式で表現するためには、スプレッドシートの表示形式も変更しておく必要があります。
そうしないと、たとえば25:11というデータは自動的に01:11という時刻形式に変換されてしまいますので。

表示形式の選択

まずは、時分を表示させるセルを選択して、[表示形式] – [数字] – [カスタム数値形式]を選択。

カスタム数式で「[h]:mm」と入力し、『適用』をクリックする

スプレッドシートの設定が完了したら、あとはGASで時分に変換するだけです。
ここであれこれ考えこんでしまいましたが、シンプルに時間と分を別々に計算。
その上で、文字列として結合するという方法を選択しました。

workingTimeはミリ秒なので、1000で割るということを忘れずに。

GAS
  // 時・分で換算する
  const hour = Math.floor(workingTime / 60 / 60 / 1000);
  const minute = Math.floor((workingTime - hour * 3600000) / 60 / 1000);

  // [h]:MM形式で文字列として設定する
  const workingTimeStr = hour + ":" + minute + ":00.000";

workingTimeStrで最後に“:00.000”という文字列をくっつけているのは、分が1桁の場合に44:0のように表示されてしまうことがあるため。
スプレッドシートの表示形式に合わせる形で、“:00.000”を最後にくっつけています。

あとは、workingTimeStrを対象セルにsetValueすれば完了です。


おわりに

Googleフォームで回答したデータから勤務時間を計算し、[h]:mm形式に変換する場合のポイント
  • スプレッドシートの表示形式を[h]:mmにして、24時間以上の時間も表示できるようにする。
  • GASでミリ秒から時分に変換する場合には、時分それぞれ別々に計算して、最後に結合させるようにする。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA