1. Home » 
2. エクセル関数の技 » 
3. 日付に関する技/月末など指定日を求めたい

日付に関する技/月末など指定日を求めたい:Excel関数の技

月末を求める　　topへ

• DATE関数を使う
  • Excelでは日付はシリアル値という値で関連付けられています。
  • 「月末とは次の月の1日の前日である」と考えれば容易な式で求めることができます。
  • 例えば、2009年2月末日なら、2009/3/1の前の日と考えます。
    DATE関数を使って　=DATE(2009,3,1)-1　→　=DATE(2009,3,0)と書くことができます。
  • A列の日付の月末を求める例
    • 年はYEAR関数、月はMONTH関数で求め「+1」して次の月とします。日にちは前日なので「0」とします。
      
• EOMONTH関数を使う
  • Excel2003以前では、この関数を使うには分析ツールのアドインを有効にしておく必要があります。
    ［ツール］→［アドイン］を選択し、［アドイン］ダイアログボックスの「分析ツール」にチェックを入れます。
    環境によってはExcel（Office）のセットアップ媒体を要求されますので、メッセージの指示どおりに操作してください。
  • =EOMONTH(開始日,月)
    開始日から起算した月数を指定します。月に正の数を指定すると、起算日より後の日付を返し、負の数を指定すると、起算日より前の日付を返します。
  • 当月末日は=EOMONTH("2009/2/10",0)　とか　先月末日は=EOMONTH("2009/3/10",-1)　とすればよいでしょう。
  • A列の日付の月末を求める例
    • 専用の関数なので、シンプルな式になります。ポイントは「月」の指定の仕方でしょう。
      

  土日であったら前営業日としたい

  • 次の項で説明するWORKDAY関数を利用します。ここでは結果を示します。
  • 例えば、2009/5/31は日曜日ですので5月末の営業日は2009/5/29となります。
    これを求めるには翌月の1日から前方向へ営業日を探します。　=WORKDAY(EOMONTH(A2,0)+1,-1)　とします。
    

  土日祝祭日であったら前営業日としたい

  • このような月末はないだろう・・・と思いましたが、2007年4月29日が昭和の日で日曜日にあたり、翌30日は振り替え休日でした。
    
  • 次の項で説明するWORKDAY関数を利用します。また、祝日のリストが必要になります。
    なお、祝日のリスト範囲には「祝日」と名前を定義しています。
    • 土日祝日を除いた翌営業日は=WORKDAY(EOMONTH(A2,0)+1,-1,祝日)　で求ることができます。
      

締日を求める　　topへ

• 月の締日が20日でこの日を超えると翌月の20日が締日となるとします。
• 日付が20日以前であれば「その月の20日」が締日で、20日を超えると「翌月の20日」が締日なので、IF関数で場合分けすれば良さそうです。
  • =IF(DAY(A1)<=20,DATE(YEAR(A1),MONTH(A1),20),DATE(YEAR(A1),MONTH(A1)+1,20))
    
  • 条件を「DAY(A1)>20」を満たすか否かとすれば、満たす時は[True、1」となり、満たさないときは「False、0」となるので
    =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>20),20)　とすることができます。
    • 結果が0かもしくは1を足すのですから、この条件に合う論理式を入れればよいということです。
      1となるのは日が20を超えたときですから(DAY(A1)>20)というのが考えられます。超えなかったらFalseで0となり都合がよいわけです。
    

• 土日であったら翌営業日としたい

  • 次の項で説明するWORKDAY関数を利用します。ここでは結果を示します。
  • 例えば下図のように、2009/6/20は土曜日なので、土日を除いた翌営業日は2009/6/22としたい。
    2009/7/20は月曜日なので、土日を除いた翌営業日はそのまま2009/7/20でよいのですが、2009/7/20は海の日で休日になっています。
    この場合は土日祝を除いた2009/7/21としたい。
    
    • 土日を除いた翌営業日は=WORKDAY(B2-1,1)　で求めます。締日の前日から数えて次の営業日を求めます。
      これに、締日の計算式を入れこむと、=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>20),20)-1,1)　となります。
      

  土日祝祭日であったら翌営業日としたい

  • 次の項で説明するWORKDAY関数を利用します。また、祝日のリストが必要になります。
    なお、祝日のリスト範囲には「祝日」と名前を定義しています。
    • 土日祝日を除いた翌営業日は=WORKDAY(B2-1,1,祝日)　で求めます。締日の前日から数えて次の営業日を求めます。
      これに、締日の計算式を入れこむと、=WORKDAY(DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>20),20)-1,1,祝日)　となります。
      

営業日を求める　　topへ

• ある指定日から、土日を除いた5日後の営業日(平日)の日付を求めたいとします。
  • WORKDAY関数が利用できます。
    • Excel2003以前では、この関数を使うには分析ツールのアドインを有効にしておく必要があります。
      ［ツール］→［アドイン］を選択し、［アドイン］ダイアログボックスの「分析ツール」にチェックを入れます。
      環境によってはExcel（Office）のセットアップ媒体を要求されますので、メッセージの指示どおりに操作してください。
  • =WORKDAY(開始日,日数,祭日)
    たとえば、2009/4/28から5日後の日付を求めます。
    カレンダーはこのようになっています。
    

  • 土日を除いた営業日を求める

    • 上の表の配置でしたら、=WORKDAY(B6,5)　となります。
      日付を指定するときは　=WORKDAY("2009/4/28",5)　とか　=WORKDAY(DATE(2009,4,28),5)　とします。
      これで、「2009/5/5」が求められます。

    土日祝祭日を除いた営業日を求める

    • 祝日はリストを準備します。
      Sheet2のA1:A17に祝日のリストを作成し、この範囲A1:A17に「祝日」と名前を定義しておきます。
      
    • =WORKDAY(B6,5,祝日)　とすると計算できます。
      日付を指定するときは　=WORKDAY("2009/4/28",5,祝日)　とか　=WORKDAY(DATE(2009,4,28),5,祝日)　とします。
      これで、「2009/5/11」が求められます。

期間の営業日数を求める　　topへ

• ある期間から、土日を除いた営業日(平日)の日数を求めたいとします。
  • =NETWORKDAYS(開始日,終了日,祭日)　を使います。
    • Excel2003以前では、この関数を使うには分析ツールのアドインを有効にしておく必要があります。
      ［ツール］→［アドイン］を選択し、［アドイン］ダイアログボックスの「分析ツール」にチェックを入れます。
      環境によってはExcel（Office）のセットアップ媒体を要求されますので、メッセージの指示どおりに操作してください。
  • たとえば、2009/4/28から2009/5/12の営業日数を求めます。
    カレンダーはこのようになっています。
    

  土日を除いた営業日数を求める

  • 上の表の配置でしたら、=NETWORKDAYS(B6,B21)　となります。
    日付を指定するときは　=NETWORKDAYS("2009/4/28","2009/5/13")　とか　=NETWORKDAYS(DATE(2009,4,28),DATE(2009,5,13))　とします。
    これで、「12」が求められます。

  土日祝日を除いた営業日数を求める

  • 祝日はリストを準備します。
    Sheet2のA1:A17に祝日のリストを作成し、この範囲A1:A17に「祝日」と名前を定義しておきます。
    
  • =NETWORKDAYS(B6,B21,祝日)　とすると計算できます。
    日付を指定するときは　=NETWORKDAYS("2009/4/28","2009/5/13",祝日)　とか　=NETWORKDAYS(DATE(2009,4,28),DATE(2009,5,13),祝日)　とします。
    これで、「8」が求められます。

第2月曜日、第3月曜日の日付の求め方　　topへ

• 成人の日」「海の日」「敬老の日」「体育の日」は第2月曜日、第3月曜日の祝日を計算で求めたいというケースがあります。
• カレンダーを作成してみます。
  
• 月の初め「1日」の曜日のWEEKDAY関数の返り値と第2月曜日、第3月曜日の関係は下図のようになります。
  
  • 2009年10月の体育の日(第2月曜日)は=CHOOSE(WEEKDAY(A1),9,8,14,13,12,11,10))　で求めることができます。
    
  • 2009年9月の敬老の日(第3月曜日)は第2月曜日+7となりますので、=CHOOSE(WEEKDAY(A1),9,8,14,13,12,11,10)+7)　で求めることができます。
    
• 上の関係をWEEKDAY関数の種類を3にすると
  
  更に、この関係の曜日が1つ右にずれて↓のようになれば都合がよいのですが・・・
  つまり、「1日」の前の日の曜日を使えば都合がよくなります。
  
  • 1日の前の日はDATE(年,月,0)となりますので、
  • 第2月曜日は=14-WEEKDAY(DATE(YEAR(A1),MONTH(A1),0),3)　とすることができます。
  • 第3月曜日は+7すればよいので、=21-WEEKDAY(DATE(YEAR(A1),MONTH(A1),0),3)　とすることができます。

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27