Excel やその他のスプレッドシートで日付を使用して計算する

時間を節約し、日付の計算エラーを回避するには、Microsoft Excel、Google Sheets、LibreOffice Calc、OnlyOffice Spreadsheets で利用できるこれらの非常に実用的な関数を使用します。

日付の操作は、私たちが毎日何も考えずに行う些細な操作のように思えるかもしれません。しかし、私たちのカレンダーは、思っているよりも複雑なシステムです。 5 月 12 日から 28 日までの日数を数えるために電卓を取り出す必要はありませんが、少し難しい計算に直面することがよくあります。 2月8日から11月17日までは正確に何日ありますか?そして、これら 2 つの日付の間だけが営業日なのでしょうか?それとも、例えば水曜日や祝日を除いた暦日ですか?

幸いなことに、この種の場合、コンピュータツールを使用すると作業が大幅に容易になります。マイクロソフトエクセル、Google Sheets、LibreOffice Calc、または OnlyOffice Spreadsheets には、これらの計算に役立つすぐに使える関数が備わっています。このソフトウェアで日付の計算を実行するために利用できる多数の関数の中で、特に便利で、頭の回転を数秒短縮するだけでなく、個人的または仕事上の時間を大幅に節約できる 4 つを選択しました。

さらに、これらの関数は、最も広く普及し使用されている前述の 4 つのスプレッドシートに同一に存在するという利点があります。これらの構文と操作はアプリケーション間で同じであるため、使用できるソフトウェアに関係なく使用できます。

この実用的なガイドでは、スクリーンショットは Microsoft Excel から取得されていますが、すべての機能は Google スプレッドシートでもテストされています。リブレオフィス計算とOffice スプレッドシートのみ。

最も単純なシナリオから始めましょう。2 つの日付を区切る正確な暦日数を数えます。これを行うには、関数を使用できます=DAYS(終了日;開始日):

セルA2に次のように書きます。2023 年 1 月 1 日
セルB2に次のように書きます。2023/12/31
セルC2に次のように書きます。=JOURS(B2;A2)

Enter キーを押して入力内容を検証すると、セル C2 に値 364 が表示されます。

セルA3に次のように書きます。2023/08/02
セルB3に次のように書きます。2023/11/17
セルC3に次のように書きます。=DAYS(B3;A3)

Enter キーを押して入力内容を検証すると、セル C3 に値 282 が表示されます。

©CCM

最初の式の結果に驚かれるかもしれません。1 から 364 日を示します。^は2023 年 1 月と 12 月 31 日は、直感的に 365 日以内の結果が得られると予想されます。これは、Excel とほとんどのスプレッドシートでの日付の処理方法に由来しており、日付は連続したシリアル番号として保存されます。2023 年 1 月 1 日は数値 44927、2023 年 12 月 31 日は数値 45291 です。2 つの日付の間の日数をカウントするには、次のようにします。機能日()2 番目の数値と最初の数値の差、つまり 45291 - 44927 を計算します。その結果は実際に 364 になります。

DAYS関数を使って日付を「数値」形式で表示する©CCM

これを解決するには、2 つのオプションがあります。日数をカウントしたい期間の最終日の翌日、この例では 01/01/2024 および 11/18/2023 に等しい終了日を入力できます。

©CCM

この最初の方法は機能しますが、たとえば、この微妙な点を念頭に置いていない自分以外のユーザーがデータを入力する場合、混乱が生じる可能性があります。もう 1 つの解決策は、日数を数えたい期間の最終日を考慮するように計算式を変更することです。

セルC2に次のように書きます。=JOURS(B2+1;A2)
セルC3に次のように書きます。=DAYS(B3+1;A3)

Enter キーを押して入力内容を検証すると、セル C2 と C3 に 365 と 283 が表示されます。これは、ほとんどの場合、予想される結果とより一致しています。

©CCM

開始日と終了日を関数に直接書き込むこともできます。日()、括弧内にありますが、この方法はお勧めできません。この方法で書かれた値は式に「ハード」に保存され、計算を変更するには手動で変更する必要がありますが、これは実用的ではなく、特に大規模な配列の場合はエラーの原因となります。処理する日付の数。

それでも開始日と終了日を関数に直接書き込みたい場合日()たとえば、1 回限りの計算の場合は、特定の構文を尊重し、日付値を引用符で囲む必要があります。

セルA2に次のように書きます。=DAYS("2023/12/31";"2023/01/01")
セルA3に次のように書きます。=DAYS("2023/11/17";"2023/02/08")

Enter キーを押して入力を確認すると、セル A2 と A3 に 364 と 282 が表示されます。

DAYS()関数に直接日付を書き込んだ計算式©CCM

日付の前後の引用符を省略しないでください。日()これらの値を日付として識別できないため、体系的に結果 0 が返されます。

日付が引用符なしで書かれている場合の DAYS() 関数の結果©CCM

Excelで営業日を計算するにはどうすればよいですか?

たとえば、納期や応答時間、あるいは営業日数を計算する場合など、2 つの日付の間の営業日数をカウントすると便利な場合が多くあります。週末、祝日、契約上の休日などの特定の日を結果から差し引く必要があり、これらの日は国や状況によって異なる場合があるため、問題は以前よりも少し複雑になります。

2023 年のフランスの週末が土曜日と日曜日、祝日で構成されている最も一般的なシナリオから始めましょう。これらの条件で 2 つの日付の間の営業日数をカウントするには、次の関数を使用できます。=NB.WORKINGDAYS(開始日;終了日;[休日])。

DAYS() 関数と COUNTDAYS;WORKS() 関数の同じ期間の結果の違い©CCM

まず最初に、この関数では「start_date」引数と「end_date」引数の順序が逆になっていることに気づくでしょう。COUNT.WORKINGDAYS()機能に関連して日()したがって、両方の関数を定期的に使用する場合は、数式を記述するときに注意してください。

それから関数COUNT.WORKINGDAYS()という 3 番目のオプションの引数が含まれます。【休日】。この引数を使用すると、計算から除外する祝日を指定できます。オプションなので、ここに何も指定しないと、関数は期間中に祝日がないとみなします。

最後に、関数COUNT.WORKINGDAYS()関数とは異なり、計算された営業日数から終了日を除外しません。日()。したがって、追加する必要はありません+1前の例と同様に、end_date 引数に追加します。

セルA2に次のように書きます。2023 年 1 月 1 日
セルB2に次のように書きます。2023/01/31
セルC2に次のように書きます。=COUNT.WORKINGDAYS(A2;B2)

Enter キーを押して入力を検証すると、セル C2 に 22 が表示されるはずです。練習するには、列 A と列 B に 2023 年の各月の初日と最後の日を指定して次の行を入力し、セルから数式をコピーします。 C2 を他の行に追加すると、次の結果が得られます。

2023 年の月の NB.WORKDAYS() 式の結果©CCM

とりあえずの機能としては、COUNT.WORKINGDAYS()土曜日と日曜日のみを差し引いた、2 つの日付の間の日数を示します。祝日やその他の非稼働日も差し引くには、関数の参照として機能する小さな別のテーブルに祝日を入力する必要があります。

セル E1 に、次のリストをコピーして貼り付けます。
2023 年の祝日
2023 年 1 月 1 日
2023/10/04
2023/01/05
2023/08/05
2023/05/18
2023/05/29
2023/07/14
2023/08/15
2023 年 1 月 11 日
2023 年 11 月 11 日
2023/12/25
セルC2に次のように書きます。=COUNT.WORKINGDAYS(A2;B2;$E$2:$E$12)
上の式の $ 記号に細心の注意を払ってください。これらは、セル範囲 E2:E12 がセル範囲であることを示しています。絶対参照これにより、祝日のリストとのリンクが失われる危険を冒さずに、どこにでも数式をコピーして貼り付けることができます。
セル C2 からセル C3 から C13 までの数式をコピーして貼り付けます。

入力内容を検証すると、次の結果が得られます。

祝日を示す COUNT.WORKDAYS() 関数の結果©CCM

列 C には、2023 年の各月の営業日数が表示されるようになりました。したがって、列 E に示されている土曜日、日曜日、祝日は含まれません。リストに新しい祝日を追加する必要がある場合は、忘れずに計算を更新してください。列 C に式を追加し、新しい要素が関数によって考慮されるようにします。注意：営業日。

Excel でパーソナライズされた週末を含む営業日をカウントするにはどうすればよいですか?

土曜日または日曜日以外の週末または休息日を含む 2 つの日付の間の営業日数をカウントする必要がある場合は、関数を使用する必要があります。NB.WORKINGDAYS.INTL(出発日;終了日;[週末];[休日])。関数とまったく同じように動作します注意：営業日だけでなく、パーソナライズされた週末の日を指定することもできます。

©CCM

開始日、終了日、祝日については前と同じ引数が見つかりますが、次の新しい引数が追加されています。[週末]3番目の位置にあります。これにより、従来の土曜日と日曜日以外に、パーソナライズされた週末や休息日を定義できるようになります。

その構文は少し特殊です。2 つの日付の間でカウントされる日数から除外する繰り返しの日を表すコードまたは文字列の形式で記述する必要があります。以下は、考えられるさまざまなコードとカウントから差し引かれる日数との対応表です。

COUNT.WORKDAYS.INLT() 関数の [weekend] 引数に使用できるコード
コード	カウントから差し引かれる日数
1または省略 2 3 4 5 6 7 11 12 13 14 15 16 17	土曜日、日曜日日曜日、月曜日月曜日、火曜日火曜日、水曜日水曜日、木曜日木曜日、金曜日金曜日、土曜日日曜日のみ月曜日のみ火曜日のみ水曜日のみ木曜日のみ金曜日のみ土曜日のみ

構文がコードでどのように機能するかを説明するために、いくつかの例を次に示します。

式=COUNT.WORKINGDAYS.INTL(A2;B2;1;)A2 と B2 に含まれる日付間の土曜日と日曜日を差し引いた日数を返します (3 番目の位置にコード「1」)
式=COUNT.WORKINGDAYS.INTL(A3;B3;2;)A2 と B2 に含まれる日付間の日曜日と月曜日を差し引いた日数を返します (3 番目の位置にコード「2」)
式=COUNT.WORKINGDAYS.INTL(A4;B4;3;$E$2:$E$12)A2 と B2 に含まれる日付の間の日数を返します。月曜日と火曜日 (3 番目のコード「3」) およびセル範囲 E2:E12 に示されている祝日を差し引いた日数が返されます。

[weekend] 引数がコード形式でどのように機能するか©CCM

コードシステムは実用的ですが、連続した週末または休日のみを指定できます。たとえば水曜日と日曜日など、非稼働日や連続しない日を定義するには、引数を使用する必要があります。[週末]構文を文字列形式で使用します。

この場合、引数は 7 文字の文字列として引用符で囲み、値 0 または 1 を指定する必要があります。各文字は曜日を表し、最初の文字は月曜日を表し、7 番目の文字は日曜日を表します。 0 はその日が稼働日であることを示し、1 はその日が稼働していないことを示します。わかりやすくするために、いくつかの例を示します。

「0010001」は水曜日と日曜日が休みであることを意味します
「1000010」は月曜日と土曜日が休みであることを意味します
「1111111」は労働日がないことを意味します。
「0000000」は全日勤務であることを意味します

文字列構文がどのように機能するかを説明するために、いくつかの例を示します。

式=COUNT.WORKINGDAYS.INTL(A2;B2;"0010001";$E$2:$E$12)A2 と B2 に含まれる日付の間の日数を返し、セル範囲 E2:E12 で指定された水曜日と日曜日 (文字列 "0010001") および休日を差し引きます。
式=COUNT.WORKINGDAYS.INTL(A3;B3;"1000001";$E$2:$E$12)A3 と B3 に含まれる日付の間の日数を返し、月曜日と日曜日 (文字列 "0010001") およびセル範囲 E2:E12 で指定された休日を差し引きます。
式=COUNT.WORKINGDAYS.INTL(A4;B4;"0000000";)A4 と B4 に含まれる日付の間の日数を返します。休息日 ("0000000" 文字列) は差し引かれず、祝日も含まれません。
式=COUNT.WORKINGDAYS.INTL(A5;B5;"1111111";)A5 と B5 に含まれる日付の間の日数を返し、すべての曜日 (「1111111」文字列) を差し引きます。

引数の文字列構文を一見すると、[週末]は、コードにあるものよりも混乱しますが、連続しているかどうか、週の日数に関係なく、任意の一連の休み日を定義できるため、はるかに強力で柔軟に使用できます。

Excelで30日の月の日数をカウントするにはどうすればよいですか?

私たちのカレンダーの月は規則的ではなく、30 日の月もあれば、31 日の月もあり、年によっては 28 日または 29 日になる残念な傾向もあります。この不規則性を回避するために、多くのシステムは同じ長さの月で構成される「仮想」カレンダーに依存しています。たとえば、給与、サブスクリプション、または家賃は 30 日の月に基づいて計算されます。これが、1 月と 2 月に電話プランに対して同じ金額を支払う理由です。

Excel で 2 つの日付の間の日数を月 30 日ベースでカウントするには、次の関数を使用できます。ジュール360。その構文は次のとおりです=DAYS360(開始日;終了日;[方法])3 つの引数があります。

開始日など日付_フィンこれは必須であり、日数をカウントする期間の開始日と終了日を表します。直接書き込まれた日付、または日付を含むセルへの参照を含めることができます。
[方法]これはオプションで、月から 30 日までの計算に米国方式と欧州方式のどちらを使用するかを示します。この引数が空か FALSE の場合はアメリカ方式が使用され、TRUE の場合はヨーロッパ方式が使用されます。

参考までに、Excel に統合されたヘルプで提供される各メソッドの定義を次に示します。

アメリカの方法 (FALSE または省略): 米国の方法 (NASD)。開始日が月末の場合、開始日は同月30日となります。終了日が月の末日で、開始日が月の 30 日より前の場合、終了日は翌月の 1 日になります。それ以外の場合、終了日は同じ月の 30 日になります。
ヨーロッパ方式：ヨーロッパ方式。月の 31 日に対応する開始日と終了日は、同じ月の 30 日になります。

実際、計算には常にヨーロッパの方法を使用する可能性が高いため、必ず引数を入力してください。[方法]値と一緒に真実。結果として、数式は常に次のようになります。=DAYS360("01/01/2023";"12/31/2023"; TRUE)または=JOURS360(A2;B2;VRAI)

さらに、機能に注意してください。ジュール360日数の計算から期間の終了日を除外します。、したがって、希望する期間の翌日の終了日を指定するか、追加する必要があります。+1議論にdate_fin。

関数がどのように動作するかを理解するための例をいくつか示します。ジュール360:

任意のセルに次のように書きます。=DAYS360("01/01/2023";"12/31/2023"; TRUE)。この式の結果は次のようになります。359。
任意のセルに次のように書きます。=DAYS360("2023/01/01";"2023/12/31"+1;TRUE)。この式の結果は次のようになります。360。

書く2023 年 1 月 1 日セルA4では、2023/01/31セルB4では、=JOURS360(A4;B4;VRAI)セルC4にあります。この式の結果は次のようになります。29。
書く2023 年 1 月 1 日セルA5では、2023/01/31セルB5では、=DAYS360(A5;B5+1;TRUE)セルC5にあります。この式の結果は次のようになります。30。
書く2023 年 1 月 2 日セルA6では、2023/02/28セルB6では、=DAYS360(A6;B6;TRUE)セルC6にあります。この式の結果は次のようになります。27。
書く2023 年 1 月 2 日セルA7では、2023/02/28セルB7では、=DAYS360(A7;B7+1;TRUE)セルC7にあります。この式の結果は次のようになります。30。
書く2023/01/15セルA8では、2023/02/28セルB8では、=DAYS360(A8;B8+1;TRUE)セルC8にあります。この式の結果は次のようになります。46。