Excelでの時間の計算をシェルコマンドでどう取り扱うのか〜DATEの計算方法


2021/04/13

エクセルでの日付時間とシェルコマンドの扱うUNIX時間とは一見取り扱っているデータの表示が同じようで、日付の換算方法は異なります。

ここではExcel時間表示フォーマットの話と、それをシェルコマンドでどう補正するとCSVインポート・エクスポートが上手くいくのかを考察してみます。


はじめに

当サイトではオフィス業務のComputer-Aidedなハイブリッドな方法を模索し、より効率的なExcel業務を実現したい多忙なオフィスワーカー向けの主にAwkとSedを使うシェル講座です。

シェルスクリプトはどこでもどんなOSでも基本的に使えて、しかも一度使い方を覚えると、Excelと組み合わせて最高に効率の良いオフィスワークツールが作れることでしょう。

合同会社タコスキングダム|蛸壺の技術ブログ


Excelでの日付表示

ずばりExcelの日付表記方法とは、グレゴリオ暦の1900/1/1 00:00:00を1とする換算方法になりますので、例えば2021/1/1は、44917という数値が日付の実体になり、Excelデータシート上では自動で適切な日付に直して表示しているに過ぎません。

この日付を表す数値はゼロ以上とする必要があり、ちなみに0とすると、1900/1/0と正体不明のゼロ日が出現します。

ということで、日付データを含むエクセルデータシートをCSVへエクスポート・インポートするときには、Excel側の日付ルールを適用させる必要があります。


Dateコマンド使った変換

Excel時間への変換では、1900/1/1から現在までの日数差を計算するだけですが、実際に暦を正しく計算するのは生半可な計算では処理できず、とてもではないですが簡単に自作できるレベルを超えております。

しかし、Linuxならば必ず使えるであろう、
Dateコマンドを利用することで日付の計算は楽ちんで行うことが可能です。

なお、以前の記事で
(Busyboxの)Gnu dateコマンドの簡易的な使い方を解説しておりました。一言でdateとはいってもLinuxOSのディストリビューション単位でGnu dateかそうでないかは異なる場合があります。このブログではDateコマンドと言えば、Gnu dateとさせて頂きます。

もしMacOSなどで試されたい方は、OS標準のdateの作法に従って修正してもらうか、Gnu dateをインストールして利用するかなどの対応をしてください。

以降ではこのDateコマンドでExcel時間に変換・逆変換のテクニックを具体的なスクリプトの実装例で考えていきます。

Csv > Xlsxデータインポートのとき

ここではEpoch時間を利用して日付のExcel時間の表記にしてみます。

なお1900年01月01日が1ということは1900/01/00(=1899年12月31日)をゼロですので、そのEpoch時間でいうと
-2209075200です。

            
            $ echo '2021年01月01日'|
    sed -r 's/([0-9]{4})[^0-9]+([0-9]{1,2})[^0-9]+([0-9]{1,2})[^0-9]+/\1-\2-\3/g'|
    awk -F"," '
    function parse_epochtime(rawtime) {
        #👇Awk内部で外部のコマンドを利用できる
        cmd="date -u --date=\""rawtime" 00:00:00\" +\"%s\"";
        cmd | getline parsed_time
        close(cmd)
        #👆getlineで呼び出した外部コマンドは自動では閉じないので明示にclose関数で閉じる

        #👇1900/01/00とのEpoch時間差を取得
        parsed_time = (parsed_time + 2209075200)/60/60/24 + 1;

        return parsed_time;
    }
    {
        epochtime = parse_epochtime($1);
        print epochtime
    }
'
44197
        
あってそうです。

Xlsx > Csvデータエクスポートのとき

ではエクセル時間(数値)をEpoch時間に一旦換算してから日付へと変換します。

こちらもAwkベースで考えてみます。

            
            $ echo '44197' | awk -F"," '
    function parse_excel_date(rawtime) {
        #👇1900/01/00とのEpoch時間差を取得
        parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;

        #👇Awk内部で外部のコマンドを利用
        cmd="date -d @"parsed_time " +\"%Y年%m月%d日\"";
        cmd | getline excel_date; close(cmd);

        #👇Awkでは選択肢としてstrftimeを利用しても可能
        #excel_date = strftime("%Y年%m月%d日", parsed_time);

        return excel_date;
    }
    {
        print parse_excel_date($1);
    }
'
2021年01月01日
        
ということで、エクセル時間表記からでもただし日付に戻すことも可能になりました。


まとめ

今回はシェルコマンドでExcel時間表記を変換・逆変換する場合のdateコマンドによる補正方法を解説しました。

変換のルールさえ押さえておけば、あとは自前で応用が色々と効くはずです。

参考サイト

Excelで扱う日付・時間についてまとめ

記事を書いた人

記事の担当:taconocat

ナンデモ系エンジニア

主にAngularでフロントエンド開発することが多いです。 開発環境はLinuxメインで進めているので、シェルコマンドも多用しております。 コツコツとプログラミングするのが好きな人間です。