Excelでの時間の計算をシェルスクリプトでどう取り扱うのか〜DATEの計算方法

エクセルでの日付時間とシェルコマンドの扱う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をインストールして利用するかなどの対応をしてください。

【Linuxコマンド入門】dateコマンドを徹底解説！システム・ハードウェアクロックから活用事例まで

Linux上で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で扱う日付・時間についてまとめ