はじめに
エクセルで日付形式を扱う上で、必須の知識であるシリアル値の理解を深めるための記事です。
勤怠管理表（今ではめったに無いかもしれませんが）、工数管理表、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
シリアル値を用いた時刻の扱い方
時刻をシリアル値に変換する
時刻をシリアル値に変換する場合、変換元の形式が「文字列」か「ユーザー定義」かによって変換方法がことなります。
- 「ユーザー定義」形式の日付をシリアル値に変換する。
方法：対象のセルを選択して「Crtl + Shift + ^」を押下
例）12:30:00 → 0.520833333333333
- 「文字列」形式の時刻をシリアル値に変換する
方法：TIMEVALUE 関数を用いる
例）「= TIMEVALUE("0:15:30.111111")」 → 0.010765174
例にあげた通り文字列形式であれば、ミリ秒が入っていても、その値がシリアル値として反映されます。
ミリ秒まで入った値をシリアル値で扱うのであれば、ユーザー定義では値が繰り上がってしまうので、 あらかじめ文字列形式で入力しておく必要があるということですね。
シリアル値を時刻に変換する
シリアル値を時刻に変換する方法も、ほとんど日付と同じです。
ただしミリ秒を含むシリアル値は変換すると四捨五入されます。
例えば、12:30:00.999に相当するシリアル値(0.521192)は、変換すると12:30:01になります。
- シリアル値を時刻に変換する
方法：対象のセルを選択して「Crtl + Shift + #」を押下 例）0.0107651736111111 → 1900/1/0 0:15:30
上記の通り、「1900/1/0」が入りますので、時刻のみ表示したい場合、ユーザー定義表示形式から「hh:mm:ss」を選択すると、 「0:15:30」と表示されます。
いったんまとめ
ここまで、シリアル値についての解説でした。内容をまとめると以下の通りです。以外と押さえるべきポイントはそんなに多くはありません。
- シリアル値とは1900年1月1日を1として何日経過したかを表す数値
- 日付形式の日付、またはユーザー定義形式の時刻をシリアル値に変更するには Crtl + Shift + ^ を押下する
- 文字列形式の日付をシリアル値に変換するには、DATEVALUE 関数を使う
- 文字列形式の時刻をシリアル値に変換するには、TIMEVALUE 関数を使う
- シリアル値を日付(日付と時刻)に変換するには Crtl + Shift + # を押下する
注意事項として、基本的にミリ秒を表す値は、変換すると四捨五入されるが、
TIMEVALUE関数を使った文字列→シリアル値への変更の場合は値が反映される。
応用～日付と時刻を計算する方法～
最後に少し応用として、例えば「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」に変わります！
本記事でシリアル値についてふれるのは以上ですが、上記の内容で大体の応用はきくのではないかと思います。
以上、エクセルにおけるシリアル値の解説でした。最後まで御覧頂きありがとうございました。
