Excelの日付（数値（シリアル値））を日付文字列にフォーマットする

　こんにちは。ディマージシェアの技術担当です。今回はExcelの日付をプログラムから扱う際の注意を紹介します。

Excelの日付シリアル値について

　Excelのセルにおもむろに日付を入力すると、見た目は西暦の見慣れた文字列になりますが、内部的にはただの整数（シリアル値）として扱われます。Microsoftの公式ドキュメントにも記載されています。適当なセルに日付を入力し、セルの書式設定から標準を選択すると、内部の数値が見られます。
2022/7/1は内部的には44743という数値のようです。

公式ドキュメントによれば、1900/1/1からの相対的な日数を数値として持っているようです。1900/1/1のセルの数値を確認してみると、1となっていることがわかります。

1から始まっているので、(44743 - 1)を足せば、先の2022/7/1になるはずです。

予定通りです。Excelで動作確認ができました。プログラムから使用する際は適当な日付ライブラリを用いて(シリアル値 - 1)を1900/1/1に足せば良さそうです。

公式バグ

　とりあえず1900/1/1にシリアル値を足してみます。言語はPHPですが、日付系ライブラリがある言語ならば何でも同じです。

<?php
include('./vendor/autoload.php');

use Carbon\Carbon;

function serialToDateformat($serial)
{
  return Carbon::parse('1900-1-1')->addDays($serial - 1)->format('Y-m-d');
}

echo serialToDateformat(44743) . "\n";

結果

2022-07-02

欲しい値は2022-07-01ですが、1日ずれています。Excelの日付フォーマットは、1900年が閏年であると誤って処理するバグがあります。公式ドキュメントにも存在します。確かに存在しないはずの1900/2/29がExcelでは日付として入力できるようです。

このバグはLotus 1-2-3との互換性のために引き継がれたものらしく、修正してしまうと1900/3/1以降の日付が全て1ずれてしまうため、修正するのが難しいみたいです。従って、Excelの日付シリアル値は1900/3/1以降、存在しない日付分（1900/2/29）1大きい値になります。Excelの事情によってソフトウェアのソースコードが変化するのはちょっと嫌ですが、仕方ありません。ソースコードの方を直します。

function serialToDateformat($serial)
{
  $carbon = Carbon::parse('1900-1-1');
  if ($serial < 60) {　
    return Carbon::parse('1900-1-1')->addDays($serial - 1)->format('Y-m-d');
  }
  if ($serial == 60) {
    return "存在しない日付です";
  }
  return Carbon::parse('1900-1-1')->addDays($serial - 2)->format('Y-m-d');
}

シリアル値で59までは存在する日付です。60が存在しない1900/2/29に対応します。これで、44743が2022-07-01に正しく変換されるようになりました。

まとめ

　Excelでは日付はシリアル値で管理され、1900/1/1からの連番であることを知識として持っている方は多いと思いますが、いざプログラムで計算してみたら1日ずれていて？マークがたくさん浮かんだ方も多くいると思います。とりあえず1ずれてるからその場しのぎで「-2」という謎コードを書いた方もいると思います。今回、詳しく調べた結果、1900/3/1以降の日付しか扱わないのであれば、「-2」の処理で正しいことがわかりました。1900/1/1～1900/2/28までの間の日付も扱わなければならない場合に関しては、本記事で紹介した通り、「60」に気を付けて処理を分岐すればOKです。