ACLでの時間計算:基本編

%E4%B8%8A%E9%87%8E%E6%A7%98-1_7627_edite

執筆者 上野 哲司

マネージャー

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

■公認会計士

■システム監査技術者

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

労務監査など、データ分析を実施するにあたり、時間に関する計算を行う場面に遭遇することがありますよね。

例えば、

・出社時刻から8時間後や退社時刻から8時間前の時間を取得したい(日付時刻±時間)

・出社時刻と退社時刻の差を取得して、勤務時間を取得したい(日付時刻ー日付時刻)

そこで今回は、「時間計算(時刻計算)」に関する情報を発信したいと思います。

なお、日付時刻に関する他の情報も発信しております。

●ACLでの日付計算:基本編

●ACLでの日付計算:関数編

●ACLでの時間計算:関数編 ※近日公開予定

●目次

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

(1)ACLにおける時間計算のやり方

(1-1)ACLでの時間計算の基本的な考え方

(1-2)時間を加減算する方法

(1-3)2つの日付時刻を比較して、その差(時間のみ)を算定する方法

(注 意)「日付時刻型」でなければ時間計算はできない!

(2)ACLにおける日付時刻型と日付計算、時間計算の制約

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

(1)ACLにおける時間計算のやり方

ACLでは、「日付時刻」型であれば時間計算をすることができます。

そこで、以下の3つについてご紹介いたします。

(1-1)ACLでの時間計算の基本的な考え方

(1-2)時間を加減算する方法

(1-3)2つの日付時刻を比較して、その差(時間)を算定する方法

(1-1)ACLでの時間計算の基本的な考え方

ACLで「時間計算」を行う場合は、シリアル値を使って計算し、必要に応じてその結果を時刻表示に戻すという考え方になります。

【ACLにおけるシリアル値とは】

ACLにおけるシリアル値とは、24時間(=1日)を1として、時刻を小数で表したものです。

そのため、12:00:00は、1日の半分(12÷24時間)が経過したという意味(=0.5)になります。

(例)2021年1月1日 12時00分00秒  ⇒シリアル値で表示すると「44196.5000000」となる。

  ※1900/1/1から44,196.5日経過した時間と考えます。

(Excelとは1日ズレますのでご注意ください。)

シリアル値を使って計算する場合は、下記のように考えることができます。

  ・1時間 = 1 ÷ 24時間

  ・1分  = 1 ÷ 24時間 ÷ 60分

  ・1秒  = 1 ÷ 24時間 ÷ 60分 ÷ 60秒

  ・x時間 = x ÷ 24時間

  ・x時間y分 =( x ÷ 24時間 )+( y ÷ 24時間 ÷ 60分 )

なお、ACLにおけるシリアル値は、整数部分と小数部分を分けて、以下のように呼んでいます。

整数部分 : シリアル日付

小数部分 : シリアル時刻

acl08-①.jpg

(1-2)時間を加減算する方法・・・「日付時刻±時間」 

ある日付時刻に、時間を加減算する場合、ACLでは演算フィールドを利用して、

「日付時刻」にシリアル値による時間を加減算した列を追加することでXX時間後の日付時刻を算定できます。

(例)出社時刻から8時間後(または8時間前)の時刻を取得したい場合

「出社時刻 +(8 ÷ 24)」といった演算式が入ったフィールドを追加することで、特定の時刻から8時間後の時刻を取得できます。

acl08-②.png

DEC関数

機能:小数点の桁数を指定された桁数にする関数。割り算などの結果が小数になる場合に使用します。

構文:DEC(①数値型 , ②小数位)

①:割り算などで小数になる可能性のある数値や数値型のフィールドを指定します。

②:小数部分(時刻部分)の桁数を指定します。

※時間計算では、小数第7位以下までを指定しないと、秒数まで正しく計算できない場合があります。

(1-3)2つの日付時刻を比較して、その差(時間)を算定する方法

2つの日付時刻の差(時間)を算定する場合、ACLでは演算フィールドを利用して、

「日付時刻」-「日付時刻」をすることで差(時間)を算定できます。

ただし、算定結果はシリアル値で表示されてしまうため、シリアル値を時刻表示にするためには、STOT関数を利用する必要があります。

(例)出社時刻と退社時刻の差を取得して、勤務時間を取得したい(日付時刻ー日付時刻)場合

演算フィールドを利用して、「退社時刻ー出勤時刻」といった演算式が入ったフィールドを追加することで、勤務時間を算定するととができます。

※休憩時間を除きたい場合は、上記演算式に休憩時間を追加すればOK!

(「退社時刻ー出勤時刻ー休憩時間」)

acl08-③.jpg

STOT関数

機能:シリアル値を時刻値に変換します。"Serial to Time" の省略形です。

構文:STOT(①シリアル値)

①:時刻表示したいシリアル値(フィールドや演算式)を指定します。

 ※小数部分(時刻部分)のみ時刻表示にする関数です。  

(注 意)「日付時刻型」でなければ時間計算はできない!

ACLでは、計算対象とする日付時刻の「データ型」が、「日付時刻型」でなければ日付計算や時間計算をすることができません。

そのため、もし、日付が入っているフィールドのデータ型が「文字型」の場合は正しく計算がされません。特に、文字型のフィールドは日付時刻型のように表示できてしまうため、データ型が正しく「日付時刻型」になっているかどうか、注意してくださいね。

データ型の確認方法

データ型は、対象テーブルを開いている状態で、《編集》メニューの《テーブルレイアウト》で確認することができます。

acl08-④.jpg

文字型だった場合の対処方法

上記のように、もし日付計算や時間計算をしたいフィールドのデータ型が文字型だった場合は、関数を利用して文字型を日付時刻型に変換することで、日付計算や時間計算を行うことが可能です。

acl08-⑤.jpg

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

(使用例)「日付時刻_文字型」フィールドを日付時刻型に変換する場合

 ⇒CTODT(日付_文字型 , "YYYY/MM/DD HH:MM:SS" )

(2)ACLにおける日付時刻型と日付計算、時間計算の制約

ACLでは日付や時刻の加減算ができますが、すべてで加減算ができるわけではなく、特に加算においてエラーとなるパターンがあります。

加算の場合:加算の場合は、「①日付+④シリアル値(日数)」でしか計算ができない!

acl08-⑥.jpg

そのため、加算する場合はシリアル値であることが必要です。

減算の場合:減算の場合は、全てのパターンで計算できます!

acl08-⑦.jpg

上記のように、計算式に「時刻」が入ってくると、計算結果はシリアル値で表示されるという制約があるため、シリアル値で表示されてしまう場合は、STODT関数やSTOT関数で日付時刻表示にする必要があります。

少しでも日付計算や時間計算のお役に立てればと思います。