日付と時刻の計算

エクセルでは日付や時刻といったデータも扱え、誕生日から年齢を算出したり、2営業日後の日付などを計算することが出来ます。

◆ 今日の日付と時刻を表示する（TODAY、NOW関数）

シートを開いた時に、セルに今日の日付を表示したい時にはTODAY（トゥデイ）関数を使います。同様に、シートを開いた時に現在の時刻はNOW（ナウ）関数で表示できます。後から開いた時に日付が変わっては困るケースでは使えません。

セルC2に「=TODAY()」と入力します。

◆ 期日までの日数や年齢を計算する（DATEDIF関数）

年齢など、２つの日付の間の日数や年数を調べたい時は、DATEDIF（デイトディフ）関数を使います。これは、引数「開始日」から「終了日」までの日数を計算し、引数「単位」で指定した単位で換算して求めます。

セルD5に「=DATEDIF(C5,$D$2,"Y")」と入力して、下のセルにコピーします。今日の日付は、すべての式に共通ですから絶対参照にしておきます。

また、期限までの日数を調べたい時は、期限の日付から今日の日付を引くだけで計算できます。エクセルでは、日付データは「1日」が「１」のシリアル値で管理されているためで、2つの日付の差が日数となります。なお、残りの日数のセルの表示形式は「標準」にしておく必要があります。

◆ 年と月の値からカレンダーを自動作成する（DATE関数）

年、月、日の値から該当する日付データを作る時には、DATE（デイト）関数を使います。例題では、「年」はセルB2、「月」はセルD2に入力されているので、セルを参照します。セルA5には月の最初の1日が入るので、「日」には「1」を指定します。

セルA5に「=DATE(B2,D2,1)」と入力します。エクセルでは日付は1日を「1」とするシリアル値で管理されていますので、日付データに1を足すことで、1日を足すことになります。つまり翌日の日付は「1」を足せば良いのです。セルA6に「=A5+1」と入力して、下のセルにこの式を相対的にコピーしていきます。
曜日までを表示したいのであれば、[セルの書式設定]から「表示形式」タブの分類を「ユーザー定義」を選択します。そして[種類]欄に「d"日"(aaa)」と入力します。

◆ カレンダーの土日に「休業日」と表示する（WEEKDAY関数）

カレンダーの日付が土日の場合、セルに「休業日」などと表示したいときは、曜日を調べるWEEKDAY（ウィークデイ）関数を使います。以前の紹介したように、引数の種類には３つのパターンがあります。土日の場合だけ何かをするようなケースでは、「2」か「3」のパターンを使うことが多いです。ここでは、「3」パターンの種類を使うことにしますので、土曜番号は「5」、日曜番号は「6」となります。

IF関数を用いて、曜日番号が５以上であれば、「休業日」と表示し、それ以外は空欄にするように指定します。セルB5に「=IF(WEEKDAY(A5,3)>=5,"休業日","")」と入力して、下のセルにコピーします。

◆ 翌日「10日」の日付を調べる(DATE、YEAR、MONTH、DAY関数)

請求書や見積書などの伝票で、期日を「起票日の翌日10日」にしたいことがあります。これを実現させるためには、日付計算の関数を組み合わせることで、「翌日10日」の日付を求められます。

例題で見ると、セルE2に起票日となる日付が入力されています。この日付から翌月の10日を指定するので、YEAR（イヤー）関数で「年」を取り出し、MONTH（マンス）関数で「月」を取り出します。この２つの値を取り出したら、DATE（デイト）関数を使ってまとめ、「日」のところを「10」と入力すると、「10日」となります。また、翌月を指定するので、MONTH関数で取り出した「月」に「1」を足すことで、翌月になります。

セルD12に「=DATE(YEAR(E2),MONTH(E2)+1,10)」と入力します。

◆ その月の月末の日付を調べる(EMONTH、DATE、EDATE関数)

有効期限を起票日の月末などとしているとき、月末の日付を表示するにはEOMONTH（エンドオブマンス）関数を使います。これは、引数「開始日」に基準となる日付を指定し、引数「月」で指定した数だけ前後した月の最終日の日付を計算します。当月を表すのであれば「0」を指定します。

セルD12に「=EOMONTH(E2,0)」と入力します。もし、来月の月末であれば、「月」に「1」を指定し、先月の月末であれば「-1」と指定します。

DATE関数を使ってEOMONTH関数と同じ結果を出すことが出来ます。今月末の日にちであれば、来月の1日から1を引けば良いという考えです。「=DATE(YEAR(E2),MONTH(E2)+1,1)-1」となります。

これに関連して、「今日の日付から1ヵ月後」の日付を求める時には、EDATE（イーデイト）関数を使います。引数「開始日」の日付から数えて、「月」で指定した数だけ前後した月の日付を計算します。

セルD12に「=EDATE(E2,1)」と入力します。

◆ 「20日締め、翌月5日払い」の支払日を計算する(DATE、YEAR、MONTH、DAY、IF関数)

商品を購入した時、代金の支払日が「20日締めの翌月5日払い」というルールになっているケースは多いです。このケースのような日付を計算するには、日付計算の関数を工夫しながら作成していきます。

まず支払日の候補となる「翌月の5日」と「翌々月の5日」の２つの日付をセルE2の日付をもとに算出します。

セルE15に「=DATE(YEAR(E2),MONTH(E2)+1,5)」、セルE16に「=DATE(YEAR(E2),MONTH(E2)+2,5)」と入力します。

支払日の２つの候補となる日付が分かれば、次はセルE2の日付が「20日以前」か「21日以降」かを判定する必要があります。IF関数の条件として、20日以前であれば、翌月の5日、そうでなければ翌々月の5日であるように表示すれば、支払日が求められます。

セルD12に「=IF(DAY(E2)<=20,E15,E16)」と入力します。

◆ 期日までの「営業日」の日数を計算する(NETWORKDAYS関数)

開始日と終了日の期間の間で、実際に作業を行う営業日が何日あるかを調べたい時は、NETWORKDAYS（ネットワークデイズ）関数を使います。開始日と終了日を指定し、祝日の日付をあらかじめ入力しておき、それを土日以外の休業日とすることで営業日が計算できます。

セルE4には、開始日から終了日までの単純な日数を計算しますので、「=D4-C4+1」と入力します。「1」を加えているのは、開始日も1日としてカウントするためです。また、当初は日付で表記されますので、セルの表示形式で「標準」を設定しておきます。
セルF4に「=NETWORKDAYS(C4,D4,$H$4:$H$7)」と入力して、下のセルにコピーします。

◆ 支払日が休日なら「翌営業日」を支払日にする(WORKDAY,DATE,YEAR,MONTH,DAY,IF関数)

「20日締めの翌月5日払い」という場合、もし支払日が休日の場合であったら翌営業日を支払日にしたい時があります。この時には、WORKDAY（ワークデイ）関数を使います。この関数はアドイン関数ですので、「分析ツール」を組み込んでおく必要があります。この例題では、支払日を「翌月4日の１営業日後」と考えます。もし5日が平日であれば4日の1営業日後は5日なので、そのまま翌月5日が支払日となります。もし5日が休日の場合は、営業日を求めるため、カウントされません。その後の土日と休日を除いた最初の平日が支払日として求められます。

まず、セルH14に「=DATE(YEAR(E2),MONTH(E2)+1,4)」と入力して翌月の場合の日付、セルH15に「=DATE(YEAR(E2),MONTH(E2)+2,4)」と入力して翌々月の場合の日付を求めておきます。次に、セルH12にセルE2の日付が20日以前であれば、翌月の場合を指定し、そうでなければ翌々月の日付を指定するように「=IF(DAY(E2)<=20,H14,H15)」と入力して、請求日の前日を算出します。
最後にセルD12に「=WORKDAY(H12,1,G6:G9)」と入力して、セルH12の日付を基に、1営業日後を求めます。

時間の合計や換算を極める

◆ 出退勤時刻から勤務時間を計算する（SUM、IF関数)

勤務時間の合計を計算したい場合は、通常合計を求める関数として使用するSUM関数で計算できます。

例題では、セルD9に「=SUM(D3:D7)」と入力して、勤務時間の合計を出していますが、よく見ると計算が合っていません。これは、通常日付データは、24時間以上の時間が表示できないようになっています。時間の合計を表示する時は、セルの[表示形式]を変更しておく必要があります。

[セルの書式設定]ダイアログボックスの[表示形式]タブを選択します。
[分類]は[ユーザー定義]を選択し、[種類]欄に「[h]:mm」と入力します。
これで、24時間以上の時間表示が可能となります。

なお、もし深夜勤務にも対応させるには、一工夫必要です。例題のように、出勤が「9：00」で、退勤が翌日の「1：00」の場合、退勤時刻のほうが小さいので、そのまま計算するとエラーになります。このような場合は、IF関数を用いて、退勤時刻のほうが小さい場合は、退勤時刻に「1」を足してから出勤時刻を引くようにします。

セルD3に「=IF(C3>=B3,C3-B3,C3+1-B3)」と入力して、下のセルにコピーします。

◆ 勤務時間を30分単位で切り上げ/切り捨てる(DAY、HOUR、MINUTE、FLOOR、CEILING関数)

給与を計算するときに、勤務時間の合計の端数を処理したいことがあります。例えば、「30分」単位で切り捨てるケースで、もし勤務時間が「20時間15分」なら「20時間」、「20時間42分」であれば「20時間30分」のように、分の部分で切り捨て「0」か「30」にすることになります。また、その逆に切り上げる場合もあります。

例題で、「30分単位で切り捨てる」ケースの場合を説明します。まず勤務時間の合計（セルD9）から、「時間」と「分」の値を取り出します。分の値はMINUTE（ミニット）関数を使いますので、セルG6に「=MINUTE(D9)」と入力します。「時間」は一工夫必要です。勤務時間が24時間を越えてしまうと1日として換算する必要があります。そこで、DAY（デイ）関数で「日」を取り出し、それを24倍したものをHOUR（アワー）関数で取り出した「時間」の値に加えます。セルG5に「=DAY(D9)*24+HOUR(D9)」と入力します。セルに「38時間」と表示されても、内部では「1日と14時間」として管理されます。

次に、取り出した「時間」と「分」の値を換算します。「30分単位で切り捨てる」場合は、時間の値はそのままで、分の値をFLOOR（フロア）関数で、引数「基準値」を30として切り捨てます。セルG8に「=G5」、セルG9に「=FLOOR(G6,30)」と入力します。給与額は、「時間」の値に時給を掛けたものでと、「分」の値に時給の60分の1を掛けたものを加えれば求められます。セルG3に「=G8*G2+G9*G2/60」と入力します。

「30分単位で切り上げる」ケースでは、CEILING（シーリング）関数で「分」の値を切り上げた結果が「60」の場合は、「時間」に１を加えます。これを条件に応じて場合分けするIF関数で指定します。

セルG8に「=IF(CEILING(G6,30)=60,G5+1,G5)」、セルG9に「=IF(CEILING(G6,30)=60,0,CEILING(G6,30))」と入力します。