ACLでの日付計算:関数編

執筆者 上野 哲司

マネージャー

■国際認定CAATs技術者(ICCP)

■公認会計士

■システム監査技術者

大手監査法人において、公認会計士として上場企業等に対する財務諸表・内部統制監査およびシステム監査業務に従事。2020年より三恵ビジネスコンサルティング株式会社に入社し、現在は、CAATs導入支援やCAATs技術者の育成に関する研修講師を担当。

データ分析を実施するにあたり、日付に関する計算を行う場面に遭遇することがありますよね。

そこで今回は、「日付に関する関数」の情報を発信したいと思います。

なお、今後は下記の情報も発信予定です。

●ACLで時間計算(時刻計算)をする~基本編

●ACLで時間計算(時刻計算)をする~関数編

●目次

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

(1)ACLで用意されている日付に関する関数一覧

(2)日付間の日数を取得する(AGE関数、WORKDAY関数

(3)指定した月の日付や月末日を取得する(GOMONTH関数、EOMONTH関数

(4)日付の範囲にあるレコードを抽出する(BETWEEN関数

(5)月名や曜日を取得する(CMOY関数、CDOW関数

​(6)データ型を変換(日付時刻型⇔文字型、数値型)する(CTOD関数、DATE関数

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

(1)ACLで用意されている日付に関する関数

 

   ​ACLでは、「日付や時刻に関する関数」が数多く用意されており、日付を扱う関数は主に以下になります。

※ACLでは、パラメーターが<>書きされている場合は省略可能であることを表しております。

上記の中でも、特に実務でもよく使う関数について、その使い方をご紹介いたします。

(2)日付間の日数を取得する(AGE関数、WORKDAY関数

 

 ●AGE関数

 ・機能:2つの日付の差(日数)を算定する関数です。

​ ・構文:AGE(①日付/日付時刻/文字 ➁<,締切日>)

①主に、日数計算対象とするフィールド等を指定します。

➁日数計算をするときの締切日(基準日)を指定します。※省略した場合は現在日付が使用されます。

 

 ●WORKDAY関数

 ・機能:指定した曜日を休日とした場合の稼働日を算定する関数です。

​ ・構文:WORKDAY(①開始日, ➁終了日 ③<,休日>)

①日数計算対象の開始日(フィールド等)を指定します。

➁日数計算対象の終了日(フィールド等)を指定します。

③休日とする曜日(英名)を3文字で指定します。※省略した場合は、土日が休日となります。

指定する場合:"Mon, Sun" など

●使用例

(A)2020年1月1日から何日間あるか確認する。(AGE関数)

(B)2020年1月1日からの稼働日は何日あるか確認する。(WORKDAY関数)

●Hint!

   AGE関数は”経過日数”を算定しますが、WORKDAY関数は”何日あるか”を算定するため、例えば、「2020年1月1日」と「2020年1月1日」を、AGE関数とWORKDAY関数で実施した場合、

(A)AGE(日付 , `20200101` )   ⇒ 0 ・・・経過日数は0日

(B)WORKDAY(日付 , `20200101` ) ⇒ 1 ・・・稼働日は1日

というように、計算される日数に違いがあるため注意が必要です。

(3)指定した月の日付や月末日を取得する(GOMONTH関数、EOMONTH関数

 

   ●GOMONTH関数

 ・機能:ある日付からXXか月先(または前)など、指定した月の日付を取得する関数です。

​ ・構文:GOMONTH(①日付/日付時刻, ➁月数)

①XXか月先(または前)の日付を取得するための基準日(フィールド等)を指定します。

➁月数を指定します。※「1」を指定した場合:1か月先、「-1」を指定した場合:1か月前

 ●EOMONTH関数

 ・機能:ある日付からXXか月先(または前)など、指定した月の月末日を取得する関数です。

​ ・構文:EOMONTH(①<日付/日付時刻> ➁<,月数>)

①XXか月先(または前)の月末日を取得するための基準日(フィールド等)を指定します。 

 ※省略した場合は今月末を取得します。

➁月数を指定します。 ※「1」を指定した場合:1か月先、「-1」を指定した場合:1か月前

 ※省略した場合は「0(基準日の月末)」になります。

●使用例

(A)売上日から3か月後の日付を取得したい(GOMONTH関数)

(B)売上日から3か月後の月末日を取得したい(EOMONTH関数)

●応用編・・・月初日や月中日を取得する

   EOMONTH関数は、「月末日」を取得する関数ですが、もしXXか月後の初日や15日などを取得したい場合は、EOMONTH関数を応用して使うこととなります。

・3か月後の月初を取得:EOMONTH(売上日 , 2 ) + 1

・3か月後の15日を取得:EOMONTH(売上日 , 2 ) + 15

①まずは、2か月後の月末を取得し、

➁次に、1日分を加算して月初にする

 ※15日の場合は、15日分を加算する

(4)日付の範囲にあるレコードを抽出する(BETWEEN関数

 ●BETWEEN関数

 ・機能:指定された日付について、日付間の範囲にあるかどうかを示す論理値(True or False)を返す関数です。

​ ・構文:BETWEEN(①値, ➁最小値, ③最大値)

①検証対象とする日付(フィールド等)を指定します。

➁日付範囲の開始日を指定します。

​③日付範囲の終了日を指定します。

 

   ●使用例

   (A)売上日が第1四半期(2020年1月1日~3月31日)のレコードを抽出する。

      ※BETWEEN関数は、主にフィルター機能など条件を指定する際に使用することが多いです。

(5)月名や曜日を取得する(CMOY関数、CDOW関数

 ●CMOY関数

 ・機能:指定された日の月名(英名:Aprilなど)を取得する関数です。

​ ・構文:CMOY(①日付/日付時刻, ➁長さ)

①月名を取得したい日付(フィールドなど)を指定します。

➁月名(英名)を表示する際の長さ(文字数)を指定します。

「April」は5文字ですが、「5」を指定した場合、January(7文字)は「Janua」というように5文字に切り詰められます。

 ※最大9文字(「September」など)

   ●CDOW関数

    ・機能:指定された日の曜日(英名:Mondayなど)を取得する関数です。

    ・構文:CDOW(①日付/日付時刻, ➁長さ)

①曜日を取得したい日付(フィールドなど)を指定します。

➁曜日(英名)を表示する際の長さ(文字数)を指定します。

 英名は3文字で表記されることが多いため、「3」を指定することをおススメします。 

※英名3文字 ⇒ Mon, Tue, Wed, Thu, Fri, Sat, Sun

   ●使用例

   (A)社員の出勤日について、月名を取得する。(CMOY関数)

   (B)社員の出勤日について、曜日を取得する。(CDOW関数)

●応用編・・・曜日を日本語(月曜日や(月)など)で表する方法

 ACLには、曜日を日本語で表示させる書式がありません。そこで、「条件付き演算フィールド」を使用することで、日本語で表記させることが可能になります。

(6)データ型を変換(日付時刻型⇔文字型、数値型)する(CTOD関数、DATE関数

 ●CTOD関数

 ・機能:文字型や数値型の日付を日付時刻型に変換する

​ ・構文:CTOD(①文字/数値 ➁<,書式>)

①日付時刻型に変換したいフィールドを指定します。

➁表示されている書式(例えば "YYYY/MM/DD"や、"YYYYMMDD" )を指定する。

   当該関数の場合、書式はしっかりと指定することをおススメします。

   ●DATE関数

    ・機能:日付時刻型の日付を文字型に変換する

    ・構文:DATE(①<日付/日付時刻> ➁<,書式>)

①文字型に変換したいフィールドを指定します。

➁表示されている書式(例えば "YYYY/MM/DD" )を指定する。

 当該関数の場合、書式はしっかりと指定することをおススメします。 

※①➁の両方を省略した場合は、本日の日付(OSの日付)が文字型で返されます。

   ●使用例

   (A)数値で記載され日付(「20200101」など)を日付時刻型に変換したい。(CTOD関数)

   

 (B)売上日(日付時刻型)から売上月(文字型)を取得したい。(DATE関数)

実務では、売上明細から月次売上高を算定するために、「年月日」から「年月」だけを取り出して集計を行うことがあります。この場合、日付時刻型から「年月」を取り出すために、DATE関数で必要な「年月」部分を文字型で取り出すことで実現できます。

日付が関わるような分析を行う場合、多くの場合は関数を利用してデータ加工を行う必要が出てくると思いますので、是非とも日付に関する関数の使い方に慣れていただき、日々のデータ分析のお役に立ててくださいね。

SanKei%E3%83%AD%E3%82%B4%2B%E3%83%AD%E3%

三恵ビジネスコンサルティング株式会社

SanKei Biz Consulting, Corp.

  • Facebook

Tel: 03-6268-9750 平日9時30分~17時30分
〒102-0074
東京都千代田区九段南1-5-6 りそな九段ビル5F

© 2020 三恵ビジネスコンサルティング株式会社