[spreadsheet] 1日の始まりの日時を求めるのに DATEVALUE を使うな！

TL;DR (3行で要約)

• DATEVALUE は引数を文字列に変換してしまう
• 1日の始まりの日時を求めるのには INT や FLOOR を用いる
• もちろん DATE(YEAR(値),MONTH(値),DAY(値)) でもよい

はじめに

1日の始まりの日付時刻を求めるには、day.js なら以下のように書けます。

const datetime = dayjs();
const day = datetime.startOf('day');

Carbon なら以下のようになります。

use Carbon\CarbonImmutable;

$datetime = CarbonImmutable::now();
$day = $datetime->startOfDay();

問題点

スプレッドシートでこれを行うには、DATEVALUE が使えそうですが、これを行うと、一度文字列に変換されてしまうため、意図した結果にならないことがあります。数値を切り捨てるのに parseInt を使ってはいけないのと似たような話ですね。

解決策

で、どうすれば良いのかというと、スプレッドシートの日時データはとある日からの経過日数を表すただの数値なので、INT や FLOOR で切り捨ててあげれば良いわけですね。欠点としては数式を入力した際のデフォルトの表示形式が日付にならないことですが、手動で書式設定をする代わりに TO_DATE で囲ってあげるという回避策もあります。

もちろん DATE(YEAR(B1),MONTH(B1),DAY(B1)) の方が分かりやすいと思うのであればそう書いても大丈夫です。

ちなみに DATEVALUE と異なり TO_DATE は 時・分・秒の情報を削除しません。

発展問題

では0時0分を求めるにはどうすれば良いでしょうか。24を乗じて切り捨てるのが簡単ですが、誤差がどうなのか気になりますね。いつか時間があればそちらも試してみたいと思います。

おわりに

スプレッドシートは自動での型変換が多く難しいです……。