みなさんこんにちは！GASおじさんです。
日時データの取り扱いはプログラミング初学者にとって関門となるポイントです。
スプレッドシートの日時データとGASの日時データとでは、その扱い方は異なるので、それぞれ区別して理解するようにしましょう。
GASにおける日時データの取り扱い方法については以下の記事を参照してください。
この記事ではスプレッドシートにおける日時データの取り扱い方法について解説していきます。
Youtubeでも解説していますので動画で見たい人は以下からどうぞ。
スプレッドシートにおける日時データの基本的な仕組み
日時データを理解するためには、その仕組みについて深く理解する必要があります。
といってもそんなに難しいものでもないので、気軽に学んでいきましょう。
日時データについては以下の3つに分類して考えるとわかりやすいです。
- 日付データ(Date型)
- 時間データ(Time型)
- 日時データ(DateTime型)
1個ずつ見ていきましょう。
日付データ(Date型)
まずは日付データの仕組みについて見ていきます。
Google スプレッドシートでは、日付や時刻は実際には「シリアル値」という数値で管理されています。シリアル値は1899年12月30日を基準日(つまり0)として計算されています。
| 日付 | シリアル値 |
|---|---|
| 1899/12/29 | -1 |
| 1899/12/30 | 0 |
| 1899/12/31 | 1 |
| 1900/01/01 | 2 |
| 1900/01/02 | 3 |
| ︙ | ︙ |
| 2024/01/01 | 45292 |
シリアル値は日付データに対して、データの表示形式を「数値」にすることで求めることができます。
ためしにA列に日付、B列にシリアル値を表示するスプレッドシートを作ってみましょう。
(1) B列にA列のコピーを用意する
(2) B列を選択して表示形式を「数値」にする
こうすることで各日付のシリアル値が求まります。
シリアル値を観察すると、
- 1899年12月30日が「0」
- 1899年12月31日が「1」
- 1900年01月01日が「2」
というように、日付が1日進むとシリアル値も1増えていることがわかります。
そのため、1899年12月29日は「-1」というように、基準日以前の日付はマイナスの数字で表されることになります。
このように、日付データのシリアル値は整数値で表されるため、日付の足し算はとても簡単です。翌日の日付を求めたい時は、日付に対して「+1」するだけでOKです。
時間データ(Time型)
次に時間データの仕組みについて見ていきます。
日付データのシリアル値が整数値で表されるのに対して、時間データのシリアル値は小数点で表されます。
| 時間 | シリアル値 |
|---|---|
| 0:00:00 | 0.000 |
| 1:00:00 | 0.042 |
| 2:00:00 | 0.083 |
| ︙ | ︙ |
| 12:00:00 | 0.500 |
| ︙ | ︙ |
| 23:00:00 | 0.958 |
| 23:59:00 | 0.999 |
- 1日が始まる0時00分00秒が「0.000」
- 1日の半分の12時00分00秒が「0.500」
- 1日が終わる23時59分00秒が「0.999」
というように、時間データは0〜1の範囲内の小数点で表されることになります。
こちらもスプレッドシートで確認してみましょう。
日付データのシリアル値は整数値だったので足し算も簡単でしたが、時間データのシリアル値は小数点なので、足し算する時は工夫が必要です。
よくある間違いは、1時間後の時間を求めようとして時間データに「+1」してしまうケースです。しかし、これをしても1時間後のデータは求まりません。
なぜこうなってしまうのかは、「シリアル値」で考えれば簡単にわかると思います。
0に1を足すと「1」になってしまいますが、実際に求めたい「1:00:00」という時間データは、シリアル値で表現すると「0.042…」という小数点です。
この「0.042…」という数字は、分数で表すと「1/24」です。したがって、1時間後のデータを求めたい場合は、時間データに対して「1/24」を足すことで求めることができます。
日時データ(DateTime型)
さて、日付データ(Date)と時間データ(Time)について個別に見てきましたが、これら2つのデータは日時データ(DateTime)という、より大きな括りで分類することができます。
このような包含関係にあるので、日付データ、時間データはそれぞれ日時データに置き換えることができます。
ためしに日付データを日時データに変換してみましょう。
日付データを日時データに変換すると、時間の部分は「0:00:00」となるのですね。
次に時間データも日時データに変換してみましょう。
時間データを日時データに変換すると、日付の部分は「1899/12/30」となるのですね。
なぜこうなるのかというと、1899年12月30日が基準日となっていて、この日のシリアル値は0だからですよね。
さて、日時データの基本的な仕組みについて理解できたでしょうか？
ポイントは「シリアル値」です。
- 1899/12/30のシリアル値は「0」である
- 日付データのシリアル値は整数値で求められる
- 時間データのシリアル値は小数点で求められる
というポイントを押さえておけば、スプレッドシートにおける日時データの取り扱いは一気に簡単になってくると思います。
スプレッドシートにおける日時データの関数一覧と使用例
スプレッドシートにおける日時データの関数一覧とその使用例について紹介します。
日時データの関数一覧
| 関数 | 内容 |
|---|---|
| TODAY() | 現在の日付を返す |
| NOW() | 現在の日時を返す |
| DATE(年, 月, 日) | 指定した年月日を作成 |
| TIME(時, 分, 秒) | 指定した時刻を作成 |
| TEXT(日時, フォーマット) | 日時データを指定したフォーマットで表示 |
| DATEDIF(開始日, 終了日, 単位) | 2つの日付の間の差分を計算 |
| YEAR(日付) | 日付データから「年」を取得 |
| MONTH(日付) | 日付データから「月」を取得 |
| DAY(日付) | 日付データから「日」を取得 |
| HOUR(時間) | 時間データから「時」を取得 |
| MINUTE(時間) | 時間データから「分」を取得 |
| SECOND(時間) | 時間データから「秒」を取得 |
使用例1: DATEDIF関数とTODAY関数で現在の年齢を求める
2つの日付の差分を計算するDATEDIF関数というものがあります。関数名の由来が分かりづらいですが、Date(日付)のDifference(差分)を求めるからDATEDIFということですね。（間違えてDATED_IFと読んでいたのは私だけではないはず…）
A2セルに誕生日、B2セルに今日の日付を用意し、C2セルに=DATEDIF(A2,B2,"Y")を打ち込みましょう。
C2セルに現在の年齢が求まりました。
これは、第3引数を “Y” としてるので、2つの日付の差分を「年数」で表してくれるのですね。（この第3引数には”M”とか”D”、つまり「月数」とか「日数」も指定できますが、私はあまり使わないですね…）
また、今日の日付に関してはTODAY関数で求めることができるので、これを直接、DATEDIF関数の第2引数に与えてもいいでしょう。
これは非常によく使う例なので丸暗記してもいいくらいですね。
使用例2: DATE関数で日付データを生成
日付データを「年」「月」「日」で分けて管理している場合、DATE関数が便利です。
A2セルに年、B2セルに月、C2セルに日を用意して、D2セルに=DATE(A2,B2,C2)と打ち込みましょう。
「年」「月」「日」と細分化されていたデータから日付データを求めることができました。
先ほどのDATEDIF関数およびTODAY関数と組み合わせれば、「年」「月」「日」の情報から現在の年齢を生成することもできますね。
使用例3: TEXT関数でフォーマット変換
あと日時データ周りでよく使うのは、TEXT関数によるフォーマット変換ですね。
たとえば、スラッシュつなぎの日付をハイフンつなぎの日付に変換したいとき。
A2セルに適当な日付を用意し、B2セルに=TEXT(A2,"yyyy-MM-dd")を打ち込みましょう。
第2引数"yyyy-MM-dd"のyyyyは年数4桁、MMは月数を2桁、ddは日数を2桁で表します。
それぞれyyとすると年数下2桁、Mとすると月数1桁、dとすると日数1桁の表示になります。
また、ハイフンの代わりに「年」「月」「日」を使うと、日本語表記にもできます。
それとよく使うのは曜日の抽出ですね。第2引数に”ddd”を指定すると曜日を取得することができます。
以上、よく使う例を3つ紹介いたしました。
まとめ
日時データの取り扱いは、スプレッドシートの機能を最大限に活用するために欠かせないスキルです。基本的な表示形式の設定から、関数を使った高度な計算まで、このマニュアルを参考にして、効率的に日時データを操作できるようになりましょう。
また、GASにおける日時データの取り扱いは以下の記事で解説していますので、必要な場合はこちらもご参照ください。
コメント