🙄

Spredsheetで次のx時を求める式

に公開
Excel
math
Google Sheets
tech

概要

次の決まった未来時間をほしい時がある。例えば 毎日8時 なら 次の8時 は 今日の8時 なのか 明日の8時 なのか。それを式で求めてみる。

なお、瞬間的に一致した時間については式では対応できなかったため、苦肉として 評価する基準時間をずらす ことで対応している負け犬。まぁ小数レベルで一致することはないと思うので、現実的には無視できるかもしれないが。

式

NOW関数やTODAY関数 など 今 を基準に評価する場合は以下の様になる。

=今 +  MOD(1 + (今日 + 目標時間) - 今), 1)
=NOW() +  MOD(1 + (TODAY() + TIME(0,8,0)) - NOW()), 1)
=NOW() +  MOD(1 + ((ROWNDDOWN(NOW(), 0) + TIME(0,8,0)) - NOW()), 1)

別の形で 基準日時 を基準に評価する場合は ROWNDDOWN() で小数を切り捨て、MOD() で小数を抽出などすることで、同様の式に当てる事ができる。

=基準日時 +  MOD(1 + (基準日 + 目標時間) - 基準日時), 1)
=基準日時 +  MOD(1 + ((ROWNDDOWN(基準日時, 0) + MOD(対象の時間, 1)) - 基準日時), 1)

検算

https://docs.google.com/spreadsheets/d/1iwBf1RxzdcEIuuKUI5qQa7cB1rFiiiA3GX8fNSXWGUc/edit?usp=sharing

解説

  • Spreadsheet (Excel) のシリアル値は 整数部が年月日
    • ROWNDDOWN() で小数を切り捨てれば年月日が取れる。
  • Spreadsheet (Excel) のシリアル値は 小数部が時分秒
    • MOD() で1の余りを取れば小数部が、時分秒が抽出できる。
  • 1を加えて1の余りを取ると、円環した小数が取れる。
    • 例) (- 0.1 + 1) % 1 ⇒　+ 0.9
    • 例) (+ 0.9 + 1) % 1 ⇒　+ 0.9
    • 360度の円で考えるとよりわかりやすい。
      • 例) (-30度 +　360度) % 360度 ⇒　270度
      • 例) (270度 +　360度) % 360度 ⇒　270度
      • 例) (-390度 +　(360度 * 100)) % 360度 ⇒　270度
  • 基準日上の目標時間 と 基準日時の差を算出し、マイナスはプラス側も持っていくことで今との差がプラスで大きくなる。
  • 算出した差を 基準日時 に加算することで、
    • まだの場合は差が小さく、そのまま今に加算することで 今日のこれからの時間 に。
    • 過ぎている (マイナス) 場合は大きなプラス差にして加算することで、 明日のこれからの時間 に。

image.png

余談

小数の余算ができるのずるい。。。。

('A｀)...

何に使うの？

GASの定期実行の実行時間が適当なので。実行時に実行トリガーを設定し直すのに。最初はJavascriptで書いていたけど。たくさんの設定情報をSpreadsheetに外だしした際に、その設定値に、日時などを扱ったテキストを関数使って持っていてGASで読み込んでいたので。したら、

  • GASはSpredsheetに依存する様にしたほうが一貫性があるんじゃないか？
  • 次回実行時間もSpredsheet側で持ってたほうが良いかも？

って思ったので関数を作った次第。

以上

Discussion