はじめに
以下の記事でシリアル値について触れましたが、日付の表示形式として今回の記事では UNIX TIMEをエクセルで扱う方法を学んでゆきます。
UNIXTIME を理解する前にまずはシリアル値をよく理解していることが前提です。UNIXTIMEをエクセルで扱う方法は結構ややこしくて難しいです。
可能な限り丁寧に順をおって説明しようと思います。
UNIXTIME とは
UNIXTIME は協定世界時(UTC)の1970年1月1日午前0時0分0秒からの経過秒数を表す数値です。
協定世界時(UTC)とは日本標準時(JST)から9時間引いた時刻のことです。
| 世界協定時間 | 日本時間 | UNIXTIME |
|---|---|---|
| 1970/1/1 0:00:01 | 1970/1/1 9:00:01 | 1 |
| 1970/1/1 0:01:00 | 1970/1/1 9:01:00 | 60 |
| 1970/1/1 1:00:00 | 1970/1/1 10:00:00 | 3600 |
| 2000/12/1 9:00:00 | 2000/12/1 18:00:00 | 975661200 |
| 2023/8/1 12:00:00 | 2023/8/1 21:00:00 | 1690891200 |
エクセルでUNIXTIMEを扱うための予備知識
エクセルでUNIXTIMEを扱うのは非常にややこしいです。
多くの説明ではいくつかのプロセスを省略している場合が多く、いきなり最終的な変換数式を紹介しているので、後で振り返ったときによくわからず混乱してしまいます。
また、もしもシリアル値をよく理解していなければ先にそちらを優先しましょう。
本記事では以下の２つの方法を説明します。
- エクセル日付 → UNIXTIME への変換
- UNIXTIME → エクセル日付への変換
が、その前にまず押さえておきたい数値があるのでそちらを先に順番に紹介します。
① 1日を秒で表す
一つ目は1日を秒で表すと何秒か、です。これは簡単ですね。
24×60×60 = 「86400」です。
② 9時間を秒で表す
２つ目は「9時間」を秒で表すとどうなるかです。これも簡単です。
9×60×60 = 「32400」です。
③ 1970/1/1 のシリアル値
３つ目は 1970/1/1 をシリアル値で表したらどうなるかです。
これにはエクセルのDate関数を使います。
- 「=Date(1970,1,1)」 → 1970/1/1
- 「日付」形式にかわっているはずなので「標準」に戻す → 25569
1970/1/1 はシリアル値で「25569」になります。
以上がエクセルで日付を扱う上で押さえておくべき数値です。出来れば先に頭に入れておくといいと思います。この数値があいまいなままだと多分混乱します。
エクセル日付 → UNIXTIME への変換方法
ではようやく本題に入ります。
まずエクセルの日付というのは実体はシリアル値である、ということを押さえておきましょう。
つまりシリアル値をUNIXTIMEに変換するということです。
A1に日付が入っているとすると、以下の数式で変換できます。
「= (A1 - 25569)*86400 - 32400」
簡単に解説すると、
シリアル値は1900/1/1から、UNIXTIMEは1970/1/1からのカウントする数値です。
なので 1900/1/1 ～ 1970/1/1 までの数値を除外しておきます。 → 「A1 - 25569」シリアル値は日数、UNIXTIMEは秒数を1としてカウントします。
なので 86400 をかけます。最後にUNIXTIMEは日本時間から9時間引いた時刻なので 32400 をひきます。
以上です。かなーりややこしいですね(^^;)
UNIXTIME → エクセル日付への変換への変換方法
秒でカウントするUNIXTIMEを日数でカウントするシリアル値に変換します。
A1にシリアル値が入っているとすると、以下の数式で変換できます。
「=(A1+32400)/86400 + 25569」
一応解説すると、
- UNIXTIMEは日本時間よりも9時間遅れているのでその分を足します。→ A1 + 32400
- 日換算に直すため32400で割ります。
- 1900/1/1 ～ 1970/1/1 までのシリアル値を足します。
そして最後に、ユーザー定義で日付形式に変換します。
yyyy/m/d h:mm:ss
以上です。
記憶する必要は全然ないと思いますが、理解できるなんども試したり記事を読み返して頂ければ幸いです。最後までご覧頂きありがとうございました。
[2024/05/26追記]
実はUNIXTIMEの計算は、今回のようにエクセルを使うよりも、JavaScriptを始めとするプログラムを用いる方がずっと簡単です。別の記事を書いたのでよろしければご覧ください。
