「2つのExcelブック間で日付をコピーペーストすると、謎のずれが発生する」という問合せをいただきました。
ときどき発生する問題ですね。
Excelの日付には基準日という起点があり、それが2種類あるというお話です。
今回の環境は、Windows 10 Pro/Home、Excel 2013です。
ただし、対象ブックは.xlsで、Excel 2000くらいの環境で作られたものと考えられます。
現行のExcelすべてに当てはまる話です。
問題
あるブックから別のブックに日付をコピーすると、必ず日付と曜日が変わってしまう（ずれてしまう）ということです。
お問合せ内容を聴く限りでは、シートには日付と曜日をそれぞれ表示しているだけです。
そこで、年までが変わっているということに気付かなかった模様です。
セル内のデータ（値）を確認してもらって、年も変わっていることが判明しました。これで法則性があることも分かりました。
つまり、シリアル値が異なっていたのです。
原因
基準日付が原因です。
Excelには、基準日付が2種類あります。
これは、オプションの詳細設定で変更可能です。（後述）
設定を変更してしまったのか、ブック作成時のデフォルト設定を受け継いでしまったのかは不明です。
問題になったブックは、かなり古いものでした。作成者の名前を知らないようなシロモノですｗ
おそらく、デフォルト設定を継いでしまったのでしょう。
マックで作られたものと判断しました。
確認方法
それぞれのブックで、以下の設定を確認します。
ファイル → オプション → 詳細設定
詳細設定の下の方です。
「次のブックを計算するとき」のところにある「1904年から計算する」のチェックボックスのオンオフが異なっているはずです。
「次のブックを計算するとき」と書いてある右側のプルダウンメニューでブックを選べるので、切り替えながら確認してください。
ここの設定が同じ場合は、原因は他にあります。
書式設定などを確認しますので、別途お問合せください。
基準日付の話
マメ知識です。
マクロを書く方にはなじみ深いと思いますが、Excelでは日付をシリアル値で管理しています。
基準日から何日離れているかという整数値です。
多くの場合は正の整数です。
Excelには、1900年1月1日と1904年1月1日の2種類の基準日付があるのです。
前者はMS（マイクロソフト）の標準で、後者はマックの標準という説明になっています。
Windowsのみを使っていれば、特別な設定を入れない限りは、1900年1月1日が基準になります。
具体的には、Microsoftの場合は1900年元日が「1」で、Macintoshの場合は1904年元日が「1」なのです。
うるう年を挟みますので、両者（両社）のズレは4年と1日です。
中途半端に値が変わっているように見えてしまうのは、このためです。。
コピペで何が起こるのか
具体伊的に、日付が「2」の時を考えてみましょう。
Microsoftの基準では、1900年1月2日（火）です。
Macintoshの基準では、1904年1月2日（土）です。
データの見た目（解析結果）は違うのですが、中身は「2」というシリアル値なのです！
前者（前社： MS）のExcelにある日付を後者（後社： Mac）のExcelにコピペするとき、渡される値は「2」なのです。
それぞれの基準で解析した結果は異なってしまいますね。
実際に、Excelのセルに「2」と入力して、セルの書式設定の表示形式を「日付」に変更してみると分かりやすいと思います。
対応策
日付がズレてしまう場合の解決方法を2種類ご紹介します。
ひとつめは、貼り付けオプションを利用する方法です。
シリアル値なので、単純計算で調整できます。
その場の対処療法です。
もうひとつは、最初から、設定をそろえておくことです。
少し手間ではありますが、長期的には取り回しがよいです。
貼り付けオプションを活用する
貼り付けて変わってしまった日付に対して、値を加算するか減算するかして調整します。
その時の値は、『1462』です。
すでに他にも日付がいろいろ入ってしまっているブックの場合には、対処療法という選択肢のみです。
準備と調整に分けて操作を書きます。
準備
- 空白のセルに『1462』を入力
- 対象セルをコピー
コピー状態を維持してください。
調整
- 日付が変わってしまったセル（複数の場合は範囲を選択）を右クリックして、「形式を選択して貼り付け」をクリック
- 貼り付けグループ内の「値」を選択
- 演算グループ内の「加算」または「減算」を選択（*）
- 「OK」をクリック
（*）1900年からコピーして1904年に貼り付け変わってしまった日付には、減算貼り付けを選択し、1904年からコピーして1900年に貼り付けて変わってしまった日付には、加算貼り付けを選択するという寸法です。
設定をそろえておく
既存ブックに、問題になりそうな日付が入っていない場合や、限定的で修正可能な範囲ならば、元から正してしまいましょう！
この場合、既存の日付が変わってしまいますので、修正漏れがないように気を付けてください。
OSが異なっている環境で新規作成した場合には、基準日付の設定を確認しておくと、後で問題にならなくてよいです。
さいごに
Excelでの日付コピペ時には、ときどき、ズレが生じることがあります。
その原因と対処に関しての備忘録です。
理想としては、設定をそろえてしまうことで、問題の再発を根本的に防止したいところです。
ただし、すでにたくさんの日付が設定されてい待っているような既存ブックであれば、都度の対処にならざるを得ません。
『1462』を加算（プラス）するか減算（マイナス）するかして、対処しましょう。
コメント