はじめに
エクセルで日付形式を扱う上で、必須の知識であるシリアル値の理解を深めるための記事です。
勤怠管理表（今ではめったに無いかもしれませんが）、工数管理表、CSVデータのタイムスタンプ、等々さまざまなシーンで登場します。
自分はよく理解していないまま、たまーに必要な時がありよく混乱していました。
簡単なようで実は結構難しく、知識が定着するまでに結構時間がかかるものだと思います。
以下、シリアル値の基本概念から、知っていると便利というか最低限押さえておくべき計算方法を紹介します。
シリアル値とは
1900年1月1日を1として何日経過したかを表す数値です。
何度も復唱して暗記しておきましょう！ 秒でも分でも時間でもありません、日数です。
| 日付 | シリアル値 |
|---|---|
| 1900/1/1 | 1 |
| 1900/1/2 | 2 |
| 1900/12/31 | 366 |
| 1901/1/1 | 367 |
| 2023/8/14 | 45152 |
1日が1なので、1秒、1分、1時間はそれぞれ以下のように表せます。
- 1時間：1÷24 ≒ 0.04166667
- 1分：1÷(24×60) ≒ 0.000694
- 1秒：1÷(24×60×60) ≒ 0.00001157
| 日付 | シリアル値 |
|---|---|
| 0:00:01 | 0.00001157 |
| 0:00:02 | 0.00002315 |
| 1:00:00 | 0.04166667 |
| 24:00:00 | 1.00000000 |
シリアル値の考え方を覚えたらあとは次項の計算テクニックを覚えておきましょう。
エクセルでは日付と時刻は別の表示形式ですが、その実体はシリアル値であり、表示形式によって見え方が異なるだけです。
シリアル値を用いた日付の扱い方
方法は以下の通りです。
時刻計算の方も同様ですが、どこにダブルクオートをつけるかは要注意です。
あと値が入っているところはセル番地（A1）などでもOKです。
日付をシリアル値に変換する
日付をシリアル値に変換する場合、変換元の形式が「日付」か「文字列」かによって変換方法がことなります。
「日付」形式の日付をシリアル値に変換する。
方法：対象のセルを選択して「Crtl + Shift + ^」を押下
例）2023/8/14 → 45152「文字列」形式の日付をシリアル値に変換する。
方法：DATEVALUE 関数を使う
例）「= DATEVALUE("2023/8/14")」 → 45152
シリアル値を日付に変換する
シリアル値を日付に変換するパターンは１つです。
- シリアル値を日付に変換する
方法：対象のセルを選択して「Crtl + Shift + #」を押下
例）45152 → 2023/8/14
シリアル値を用いた時刻の扱い方
時刻をシリアル値に変換する
時刻をシリアル値に変換する場合、変換元の形式が「日付」か「ユーザー定義」かによって変換方法がことなります。
※時刻の形式はユーザー定義で設定します。
またミリ秒が入っていてもOKです。
「文字列」形式の時刻をシリアル値に変換する
方法：TIMEVALUE 関数を用いる
例）「= TIMEVALUE("0:15:30.111111")」 → 0.010765174「ユーザー定義」形式の時刻をシリアル値に変換する
方法：TEXT関数を使って「文字列」形式に変換してから TIMEVALUE 関数を用いる。
例）「=TEXT(A1,"H:MM:ss.000")」 → A1の時刻が文字列に変換される。
そのあと TIMEVALUE 関数でシリアル値に変換。
シリアル値を時刻に変換する
シリアル値を時刻に変換する方法も日付と同様１パターンだけです。
ただしミリ秒への変換できないので要注意です
（四捨五入か切り捨てられるようです、正確には検証しておらずわかりません、、。）
- シリアル値を時刻に変換する
方法：TEXT関数を用いる。
例）「=TEXT( 0.0107651736111111,"H:MM:ss")」 → 0:15:30
補足
以上がシリアル値の基本的な概念と計算方法でした。
最後に少し応用として、例えば「2023/8/1」と「12:30:00」を加算して日付時刻表示をしたい場合どうすればよいでしょうか。
以下のように一度シリアル値に変換することで日付と時刻を同列に扱うことができます。
=DATEVALUE("2023/8/1") → 45139
=TIMEVALUE("12:30:00") → 0.52083
45139 + 0.52083 = 45139.52083・・・①
①の表示形式を「ユーザー定義」で「yyyy/mm/dd h:mm:ss」に設定します。
OKボタンを押せば「2023/8/1 12:30:00」に変わります！
本記事でシリアル値についてふれるのは以上ですが、上記の内容で大体の応用はきくのではないかと思います。
最後まで御覧頂きありがとうございました。
