Excelでの日付・日時とUNIX時刻の変換

Excelの日時（シリアル値）とUNIX時刻の変換例とその説明です。
Microsoft Office Professional Plus 2019（Excel 2019）で確認しています。

ダイジェスト

Excelの日時（シリアル値）とUNIX時刻の変換を行う関数式の例です。
サンプルのExcelファイルは、githubで公開しています。

詳細の説明

分かりやすさを優先するために、秒を基準とした計算式を使っています。
少々冗長になってしまうので、実際に使用する場合はシリアル値を基準とした計算式がお薦めです。

変換の基礎

• Excelの日時は、セルに設定された「数値の書式」に応じて11月17日, 2023/11/17 13:09:57のように変わりますが、セルの値には45247.548576のようなシリアル値が格納されます。
• シリアル値は、1日は1で表現するので、1時間＝1/24、1分＝1/24/60、1秒＝1/24/60/60、となります。そのため、シリアル値に24*60*60（＝86,400）を掛けると秒数になります。
• UNIX時刻は、イギリス・ロンドンの現地時刻（協定世界時：UTC）での、1970年1月1日 00:00:00（UNIXエポック）からの秒数、です。
  • 日本時間（日本標準時: JST）は、UTC+9:00なので、日本時間から9:00（＝9*60*60＝32,400秒）を引くと、UTC基準の時間になります。
  • 1900年から1970年1月1日までの秒数（2,209,161,600秒）を引くと、1970年1月1日基準であるUNIX時刻になります。
    （1970年1月1日 00:00:00は、シリアル値で25,569です。秒数に変換すると、25,569 * 86,400 ＝ 2,209,161,600になります。）
• 上記は秒数をベースとした計算式の説明です。シリアル値をベースとするなら、9時間は9/24＝0.375、1970年1月1日は25,569になるので、=(A1-0.375-25569)*86400と表現できます。

日時（UTC）とUNIX時刻の変換

• 前述の基礎に基づいた、秒を基準として計算式、シリアル値を基準として計算式、の例です。
  
• 各計算式の説明は次の通りです。
  

  変換	関数式	説明（上段: 秒基準の計算、下段: シリアル値ベースの計算）
  日時 ↓ UNIX時刻	=A1*86400-2209161600	シリアル値を秒に変換後、基準となる1970/1/1の秒数を減算
  	=(A1-25569)*86400	1970/1/1のシリアル値を減算後、秒に変換
  UNIX時刻 ↓ 日時	=(A1+2209161600)/86400	1970/1/1までの秒数を加算後、シリアル値に変換
  	=A1/86400+25569	シリアル値に変換後、1970/1/1のシリアル値を加算

日時（JST）とUNIX時刻の変換

• 前述の基礎に基づいた、秒を基準として計算式、シリアル値を基準として計算式、の例です。
  
  • UNIX時刻はUTCの時刻を基準にしており、日本時間を前提とする日時を使用する場合、UTCと日本時間の差を考慮する必要があります。
  • 日本時間はUTCより9時間進んでいる（UTC+09:00）ので、日本時間をUNIX時刻に変換する場合は-9時間、UNIX時刻を日本時間にする場合は+9時間、の加減算が必要です。
  • 「9時間」は、秒数換算だと9*60*60＝32,400、シリアル値換算だと9/24＝0.375、になります。
• 各計算式の説明は次の通りです。
  ※基本的には前述の「日時（UTC）とUNIX時刻の変換」と同じですが、UTCと日本時間の差分（９時間）の考慮が必要です。

  変換	関数式	説明（上段: 秒基準の計算、下段: シリアル値ベースの計算）
  日時 ↓ UNIX時刻	=A1*86400-32400-2209161600	シリアル値を秒に変換後、9時間＋1970/1/1の秒数を減算
  	=(A1-0.375-25569)*86400 =(A1-25569.375)*86400	9時間＋1970/1/1のシリアル値を減算後、秒に変換
  UNIX時刻 ↓ 日時	=(A1+32400+2209161600)/86400	9時間＋1970/1/1までの秒数を加算後、シリアル値に変換
  	=A1/86400+0.375+25569 =A1/86400+25569.375	シリアル値に変換後、9時間＋1970/1/1のシリアル値を加算