Home » Excel(エクセル)の関数・数式の使い方 » 日付計算 » 土日を除いた月末最終営業日−DATE関数・YEAR関数・MONTH関数

土日を除いた月末最終営業日−DATE関数・YEAR関数・MONTH関数

対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007

EOMONTH関数・WEEKDAY関数・IF関数を使って、土曜日・日曜日を除いた月末の最終営業日を取得する方法をご紹介しました。

EOMONTH関数は分析ツールアドインに含まれる関数なので、利用を躊躇する方もいらっしゃいます。

[スポンサードリンク]

以前、EOMONTH関数を使わずに月末日を取得する方法をご紹介しましたが、その考え方を利用すればEOMONTH関数を使わずに、土日を除外した月末最終営業日を取得できます。


▼操作方法:土日を除外した月末の最終営業日を求める
※A1セルに日付を入力したときに、A6セルに月末最終営業日を表示する例

A6セルに
  =IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1,0), 2)=7, DATE(YEAR(A1), MONTH(A1)+1, -2), IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1, 0), 2)=6, DATE(YEAR(A1), MONTH(A1)+1,-1), DATE(YEAR(A1), MONTH(A1)+1, 0)))
という数式を入力する

EOMONTH関数を利用したときよりも更に長い計算式となってしまいますが、ロジック自体は同じです。

月末日を取得するのにEOMONTH関数ではなく、DATE関数・YEAR関数・MONTH関数を使っているだけです。

改行やスペースを入れて以下のようにしてみると、先日のEOMONTH関数を利用した数式と、ロジックは同じであることをご理解いただけるでしょう。

EOMONTH関数を使った数式は、
=IF(WEEKDAY(EOMONTH(A1,0),2)=7,
 EOMONTH(A1,0)-2,
 IF(WEEKDAY(EOMONTH(A1,0),2)=6,
  EOMONTH(A1,0)-1,
  EOMONTH(A1,0)
 )
)
で、今回の数式は、
=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=7,
 DATE(YEAR(A1),MONTH(A1)+1,-2),
 IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=6,
  DATE(YEAR(A1),MONTH(A1)+1,-1),
  DATE(YEAR(A1),MONTH(A1)+1,0)
 )
)
です。

これくらいの長さの数式となると、その一部を外出しにする効果はますます顕著となります。


▼操作手順:土日を除外した月末の最終営業日を求める
※A1セルに日付を入力したときに、A2セルに月末日を表示し、A6セルに月末最終営業日を表示する例

A2セルに
「=DATE(YEAR(A1),MONTH(A1)+1,0」
という数式を入力
 ↓
A6セルに
「=IF(WEEKDAY(A2,2)=7,A2-2,IF(WEEKDAY(A2,2)=6,A2-1,A2))」
という数式を入力

月末の日を求める計算式をA2セルに出してしまって、そのA2セルを参照しています。

「=IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1,0), 2)=7, DATE(YEAR(A1), MONTH(A1)+1, -2), IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1, 0), 2)=6, DATE(YEAR(A1), MONTH(A1)+1,-1), DATE(YEAR(A1), MONTH(A1)+1, 0)))」
という数式では、どの閉じる括弧がどの開く括弧と対応しているのか、どのカンマがどの関数のカンマなのかがわかりにくくなってしまいますが、
「=DATE(YEAR(A1),MONTH(A1)+1,0」
「=IF(WEEKDAY(A2,2)=7,A2-2,IF(WEEKDAY(A2,2)=6,A2-1,A2))」
という2つの数式になっていれば、わかりやすさは相当違うはずです。

▼サンプルファイル(003154.xls 72KByte)ダウンロード

サンプルファイルでは、

「DATE・YEAR・MONTH関数(1)」シートの
A6セルに
「=IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1,0), 2)=7, DATE(YEAR(A1), MONTH(A1)+1, -2), IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1, 0), 2)=6, DATE(YEAR(A1), MONTH(A1)+1,-1), DATE(YEAR(A1), MONTH(A1)+1, 0)))」
という数式が、

「DATE・YEAR・MONTH関数(2)」シートの
A2セルに
「=DATE(YEAR(A1),MONTH(A1)+1,0」
A6セルに
「=IF(WEEKDAY(A2,2)=7,A2-2,IF(WEEKDAY(A2,2)=6,A2-1,A2))」
という数式が入力してあります。

A1セルの日付を変更して、E2:E9セルにどんな値が表示されるか、そしてA6セルにどんな日付が表示されるかご確認ください。

関連語句
月の最終日を示す
[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 日付計算 » 土日を除いた月末最終営業日−DATE関数・YEAR関数・MONTH関数

TrackBack:3

TrackBack URL
土日を除外した月末最終営業日の前日を求めたい from インストラクターのネタ帳
対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007 土日を除いた月末最終営業日を取得する方法をご紹介し...
土日と祝日・祭日・休日を除いた月末最終営業日を取得−WORKDAY関数 from インストラクターのネタ帳
対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007 土日を除外した月末日関連のネタを何度かご紹介してき...
×イヤー関数、○YEAR関数 from インストラクターのネタ帳
対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007 このサイトでは、カタカナではなくAlphabet...

Home » Excel(エクセル)の関数・数式の使い方 » 日付計算 » 土日を除いた月末最終営業日−DATE関数・YEAR関数・MONTH関数

「日付計算」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.