
上図のように、任意の時間ごとに切り上げたい場合は、CEILING関数を使用する。
セルF19に「=CEILING(E19,TIME(0,$E$16,0))」と入力すると、
利用時間の「2時間46分」が、請求時間「3時間00分」となって繰り上がった。
切り上げ「=CEILING(E19,TIME(0,$E$16,0))」・・「0.125」・・・・・「3:00」
切り捨て「=FLOOR(E19,TIME(0,$E$16,0))」・・・「0.1145833」 ・・「2:45」
利用時間の「2時間46分」が切り捨てと、切り上げの違いである。
192.IF関数、TIME関数とCEILING関数で、利用時間を15分単位で切り上げる。

上図のセルF5に、「=IF(E5="","",FLOOR(E5,TIME(0,$E$2,0)))」と入力すると、「2時45分」と
表示された。これは、E列の「利用時間」「2時間47分」を15分単位で切り捨てたものである。
FLOOR関数の引数は、「=FLOOR(値,基準値)」である。基準値は「"0:15"」と記入してもよい。
これによって15分単位で切り捨てられる。数式では、「利用時間」がない場合には、何も表示し
ないようにしている。
尚、E2セルとG2セルには、数値が入っていて、「分」や「円」は表示形式で表示している。

E2セルに「15」と入力して、「セルの書式設定」を開くと「種類」に「0"分"」があれば、
それを選択し、無ければ、下図のように「種類」のテキストボックスに「0"分"」と入力する。

「200円」の場合も同じで、G2セルには、「200」と入力し、「種類」ボックスには「0"円"」
と入力して「OK」を押せば登録される。尚、[種類]に「15分」と入力するのは間違いである。

上図のセルG5に「=F5 / TIME(0,$E$2,0)*$G$2)」と入力すると、「2200円」と表示された。
これは、「TIME(0,E2,0)」でE2セルの「15分」をシリアル値に変換した値で、F5セルの
「2時45分」を割って出た結果に「200」円を掛けている。
「TIME(0,E2,0)」・・「TIME(0,15,0)」は「0.0104167」
「F5 / E2」 ・ ・・・「2時45分÷15分」は「11」
「*G2」 ・・・・・「11*200」は「2200」となる。
TIME関数の引数は、「=TIME(時,分,秒)」で指定した時間のシリアル値を求める。
191.IF関数、TIME関数とFLOOR関数で、利用時間を15分単位で切り捨てる。

上図のセルE21に、
「=IF(AND(C21>=$C$19,D21<=$D$19),D21−C21,IF(AND(C21<=$C$19,D21>=$D$19),
D21−$C$19,IF(AND(C21>$C$19,D21>$D$19),D21−C21,IF(AND(C21<$C$19,D21<
$D$19),D21−$C$19))))」と入力すると、「8:30」と表示された。
これは、「8:30」より早く出勤しても、出勤時間は8:30と決まっているから、8:30〜17:00までの
8時間30分が勤務時間となる。
計算式は、「=IF(AND(C21>=$C$19,D21<=$D$19),D21−C21,」の様に、IF関数内にAND関数を
使用して、出勤時間が規定より早いか、遅いか、退社時間が規定より早いか、遅いかの算出計算式を区別
している。

上図のセルF23に、「=IF(C23>$C$19,C23−$C$19,"")」と入力すると、「0:10」と表示された。
これは、出勤時間がC19セルの「8:30」より遅い場合に、「8:40−8:30」として「0:10」を算出
している。

上図のセルG23に、「=IF(D23<$D$19,$D$19−D23," ")」と入力すると、「0:10」と表示された。
退社時間がD19セルの「17:00」より早い場合、「17:00−16:50」として「0:10」を算出している。

上図のセルH22に、「=IF(D22>$D$19,D22−$D$19," ")」と入力すると、「0:20」と表示された。
退社時間がD19セルの「17:00」より遅い場合、「17:20−17:00」として「0:20」を算出している。
実際には、残業届が出されていない場合は、残業扱いにはならないとは思うが。

上図のセルE26に、「=SUM(E21:E25)」と入力すると、勤務時間の合計が算出される。
尚、合計時間が24時間より大きい場合には、「セルの書式設定」の「種類」で 「[h]:mm 」を
選択する必要がある。

上図の「セルの書式設定」のように、「ユーザー定義」で[種類]の中の 「 [h]:mm 」を
選択すると、「42:30」と表示される。

上図の「セルの書式設定」の様に、[ユーザー定義]で[種類]の中の「h:mm」を選択すると、
「18:30」と表示されて、24時間がカットされるので注意が必要である。
190.AND関数とIF関数で、勤務時間、遅刻、早退、残業時間を計算する。

上図は、毎日残業した時間を入力すると、規定時間に対する残り時間を算出してくれるものである。
「現在の残業時間」セルに時間と分を入力すると、「残りの残業時間」セルに時間と分が表示される。

上図のセルE9に、「=INT(((B8*60)−(C9*60+D9)) / 60)」と入力すると「43」と残り時間が
表示された。
これは、規定時間から残業時間をマイナスするのに、どちらもシリアル値に変換している。
シリアル値にするには、時間に60を掛ける必要があるので、規定時間にも残業時間にも60を
掛けている。そして、差し引きした結果を60で割ると「43.75」となるので、INT関数で整数を
取り出すと「43」となる。

上図のセルF9に、「=MOD(((B8*60)−(C9*60+D9)),60)」と入力すると「45」と表示された。
MOD関数の引数は、「=MOD(数値,除数)」であるから、INT計算とは異なる。
計算結果の「43.75」の小数部「0.75」をMOD関数で「45」と算出している。

上図のセルE10に、「=INT((((E9*60)+(F9))−((C10*60)+D10)) / 60)」と入力すると
「41」と表示された。E9セルでは、B8セルの初期値から残業時間を引いて、残り時間を
算出するが、E10セル以下は上行セルで算出した結果から、残業時間を引くことになる。
だから、E9の43時間に60を掛けて分に換算した「2580」に、F9の45分を加算した「2625」
から、C10とD10を分に換算した「155」分を減算した値「2470」を「60」で除算した結果の
「41」が残り時間となる。

上図のセルF10に、「=MOD(((E9*60)+(F9))−((C10*60)+D10),60)」と入力すると、「10」と
表示された。

上図は「=MOD(((B8*60)−(C9*60+D9)),60)」の計算過程を検証したものである。
E列とF列は、規定時間と残業時間をシリアル値に変換して減算し、残りの時間を算出したものである。
このやり方では、残り時間の「分の値」より、残業時間の「分の値」が大きい時にも「残り時間」の
繰り下げを考えなくても良い。

上図は、残業時間の合計欄である。
セルC13に、「=IF(D13>0,SUM(C9:C12)+INT(SUM(D9:D12) / 60),SUM(C9:C12))」と入力すると
「9」と表示された。これは、C9〜C12までの合計「8」と、D列の合計「105」を「60」で割った値の整数
「1」を合計したものである。「分」から1時間繰り上がっていることが分かる。
189.INT関数とMOD関数で、規定残業時間に対する残り時間を計算する。

上図のセルI2に、「=TIME(E2,F2,G2)」と入力すると、文字列が時刻となる。

そのセルI2を基にセルJ2に「=HOUR(I2)」と入力すると「13」と「時」が求められる。

セルK2に「=MINUTE(I2)」と入力すると「分」が求められる。

セルL2に「SECOND(I2)」と入力すると「秒」が求められる
188.TIME関数、HOUR関数、MINUTE関数とSECOND関数で、文字列の
値から「時」「分」「秒」を取り出す。

上図のセルB6に「=TODAY( )」と入力すると、現在の日付が表示される。

上図のセルC6に「=NOW( )−TODAY( )」と入力すると、現在の時刻が表示される。
これは、NOW関数で返された値から、TODAY関数の日付の値を差し引いたものである。
187.NOW関数とTODAY関数で、現在の日付と時刻を求める。

上図のセルB3に、「=NOW( )」と入力すると、現在の日時が表示される。
日付は、1900年1月1日を「1」とするシリアル値の連番で数え、
時刻はこの「1」日を「24(時間)*60(分)*60(秒)」で割った小数で管理されるが、
NOW関数を利用すると、その作業時点のシリアル値の「整数+小数」が返される。
尚、常に時刻が進むわけではなく、ファイルを開いたときなどに再計算される。
[セルの書式設定]で[標準]にするとシリアル値が表示される。
186.NOW関数で、現在の日時を求める。

上図のセルD18に、「=DATE(YEAR(C18),MONTH(C18)+(DAY(C18)>20)+1,0)」と入力すると、
月末の支払日が表示された。
DATE関数の引数は、「=DATE(年,月,日)」に指定した数値から、シリアル値を求める関数である。
これは、「年」を(C18)で「2024」と求め、
「月」を「MONTH(C18)+(DAY(C18)>20)+1」で求め、
「日」を「0」と入力している。
「月」の求め方の、「MONTH(C18)+(DAY(C18)>20)+1」を次の図で説明しよう。

セルD18の場合、DATE関数を分解して、E18に「YEAR(C18)」と入力すると、「2024」と
表示され、F18に「MONTH(C18)」と入力すると、「1」と表示された。
セルG18に「DAY(C18)>20」と入力すると「FALSE]と表示されて、C18セルの「日」が20日なの
で、それ以上ではないと表示された。だから、セルH18ではC18の「1」に「+1」されて「2」と表示
される。ここで、「=DATE(年,月,日)」の中の、「日」に「0」が入っているので、H18に入っている
「2月」が、その前の月の「1月」に戻されて、その月の末日が入る。
だから、セルD20の場合「=DATE(YEAR(C20),MONTH(C20)+(DAY(C20)>20)+1,0)」では、
セルG20のように「TRUE」と、日付が20日以上であると判定され、H20の様に「5月」となり、
その前の月の「4月」の月末日になる。この考え方は締切り日が20日で、支払日が月末だからである。
185.DATE関数、YEAR関数、MONTH関数とDAY関数で、締め日に応じた日付を
求める。
上図のセルD21に、「=EOMONTH(B21,C21)」と入力すると、経過後の月末日が表示される。
EOMONTH関数の引数は、「=EOMONTH(開始日,月)」と指定する。
184.EOMONTH関数で、経過後の月末日を求める。

上図のセルD17に、「=EDATE(B17,C17)」と入力すると、B17セルの日付の3ケ月後の日付が
計算された。
EDATE関数の引数は、「=EDATE(開始日,経過後又は戻る月数)」となり、何ケ月後か、何ケ月前の
日付を求めることができる。戻る日付は「マイナス」で指定し、小数点を指定しても、小数点以下は
切り捨てられる。求められた日付はシリアル値で表示されるので、表示形式を[日付]に変更する必要が
ある。
183.EDATE関数で、指定日から何ケ月後の日付を求める。

上図のセルD3に、
「=NETWORKDAYS(DATE($B$1,B3,1),EOMONTH(DATE($B$1,B3,1),0),$F$3:$H$12)」と
入力すると、1月の就業日が「18」と表示された。
NETWORKDAYS関数の引数は、「=NETWORKDAYS(開始日,終了日,祭日)」となり「開始日」と
「終了日」は、日付セルを指定するか、「"1910/1/1"」のように「" "」で囲んで入力する。
NETWORKDAYS関数は、土日を除いた日数が求められる。「祝日」や「会社休日」は別の範囲に
書いて指定すると就業日から除外される。
NETWORKDAYS関数内のDATE関数は、開始日を指定するために、EOMONTH関数で月末日付を
指定するために、ネスト構造にしている。
182.NETWORKDAYS関数、EOMONTH関数とDATE関数で、各月の就業日数を求める。

上図のセルD3に「=DATEDIF(B3,C3,"Y")」と入力すると、「5」と表示された。
DATEDIF関数の引数は、「=DATEDIF(開始日,終了日,単位)」である。
終了日は、「TODAY( )」でもよく、今日までの期間が計算される。
第三引数の「単位」は、年="Y"、月="M"、日="D"となる。
尚、期間日の式は、「DATEDIF(B3,C3+1,"D")」と終了日に「+1」するのが正解で、
「1/1〜1/2」の日数計算では、1日と求められてしまうからである。

上図のG3セルに、「DATEDIF(B3,C3,"Y")&"年"&DATEDIF(B3,C3,"YM")&"ケ月"」と入力
すると、何年何ケ月と表示される。

尚、「DATEDIF(B3,C3,"YM")&"ケ月"」と入力すると「2ケ月」と表示される。
181.DATEDIF関数で、期間内の年数、月数、日数を求める。

上図のセルD21に「=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(B21),12)+1,1)」と入力する
と「寅」と表示された。これは、干支が12種あることに着目し、
下図のように「申年」の「1968年」を12で割ると割り切れて余りが「0」になる。

だから、12で割り切れて余りが「0」になる年が基準になり、以後「余りの値」によって干支を選ん
で行けるように、指定年を12で割った余りをMOD関数で算出し、MID関数で文字列「"申酉戌亥子丑
寅卯辰巳午未"」の「余りの値+1」の位置の文字「1個」を抽出して表示している。
「余りに+1」している理由は、文字列の「0」の位置はなく、「1」の位置から始まっているからで
ある。

180.MID関数、MOD関数とYEAR関数で、誕生日から干支を求める。

上図のセルC13に、「=DATEDIF(B13,DATE(2024,5,10),"Y")」と入力すると「50」と
表示された。

上図のセルD13に、「=DATEDIF(B13,$F$13,"Y")」と入力すると「50」と表示された。
これは、DATEDIF関数の引数は、「=DATEDIF(開始日,終了日,単位)」で、引数の終了日を
セルで指定してもよい。

上図が「単位」の指定方法で、文字列なので「" "」が必要である。
179.DATEDIF関数とDATE関数で、誕生日から指定日の満年齢を求める。

上図のセルC8に、「=WEEKNUM(B8)」と入力すると「7」と表示された。
WEEKNUM関数の引数は、「=WEEKNUM(シリアル値のセル,週の基準)」と指定する。

「週の基準」は、週の初日を何曜日にするのかを、決める値であり、セルを選択して「B8,」を入力
したときに表示される。省略すると「1」が選択される。
178.WEEKNUM関数で、指定した日付が年初から何週目にあたるのかを求める。

上図のセルC2に、「=IF(MONTH(DATE(B2,2,29))=2,"うるう年","平年")」と入力すると、
「うるう年」と表示された。
「平年」は2月は28日までで、「うるう年」は2月が29日まであり、1日多い年である。

上図のセルD2に、「=MONTH(DATE(B2,2,29))」と入力すると「2」と表示された。
これは、DATE関数が、2020年の2月29日のシリアル値を求めて、算出することが出来たので、
そのシリアル値からMONTH関数が「2」と、月を取り出したものである。
だから、D2セルに「ある」と表示されるのである。
177.MONTH関数とDATE関数で、指定年が「うるう年」なのかを調べる。

上図のセルD12に、「=EOMONTH(B12,0)」と入力すると、指定月の末日が表示される。
EOMONTH関数は、月末日を算出する関数で、
引数は、「=EOMONTH(指定月,指定月〜何ケ月先か)」となり、当月が指定月の場合は「0」
で指定すると、当月の月末日を表示する。

上図のセルE12に、「=EOMONTH(B12,0)+1」と入力すると、月末日の次の、月初めの日を
表示する。

尚、「1/31(水)」と表示するには、上図の様に[セルの書式設定]で指定すればよい。
又、セルC12の様に、曜日だけを表示するには、[種類]の[aaa]を選択するか、
セルに「=TEXT(WEEKDAY(B12),"aaa")」と入力すればよい。
176.EOMONTH関数で、各月の月末日と、次月の初日を表示する。

上図のC列のように、各月の10日と20日のセルを下に複数行作成するには、オートフィルで
コピーして作成できるのが望ましい。

上図のようにセルC1に「1/10」、C2に「1/20」と入力して、二つのセルを選択して、
オートフィルで下方にドラッグすると、「2/10」とならない。
そこで、関数によって出来る方法を紹介する。

上図の様に、セルD1に「=DATE($A$1,ROW(A1)/2+0.5,10)」と入力する。
セルD2に「=DATE($A$1,ROW(A1)/2+0.5,20)」と入力する。
セルD1とD2を選択して、フィルハンドルをD8までドラッグすると、各月の
10日と20日が作成できた。
数式の「ROW(A1)/2+0.5」の意味を検証してみよう。

上図の様にセルD3に、「=DATE($A$1,ROW(A3),10)」と入力し、
セルD4に、「=DATE($A$1,ROW(A3),20)」と入力すると、
「3/10」「3/20」と表示される。これは、「ROW(A3)」は「3」となるからである。
A1、A2、A3、を使用しているのは、月数の1、2、3、の数値を使う為にROW関数で
取得している。

上図のセルE3に「=ROW(A3)/2」と入力すると、「3÷2」で「1.5」となる。
と言うことは、D3セルを「2/10」にするには「0.5」プラスすればよい事になる。

上図の様にセルD3に、「=DATE($A$1,ROW(A3)/2+0.5,10)」と入力すると「2/10」と
表示された。
セルE3に、「=ROW(A3)/2+0.5」と入力してみると「2」と表示された。
セルE5に、「=ROW(A5)/2+0.5」と入力すると「3」となった。
このやり方は、分かりやすいように「ROW(A1)」、「ROW(A3)」、「ROW(A5)」と指定して
いるので割る値が「2」になる。

尚、加える値は「0.5」から「1.4」までの範囲の値なら何でもよい。
上図のセルF3に「=ROW(A3)/2+1.4」と入力すると「2.9」と表示された。
DATE関数では月の値は、1、2、3、となりその間はないので、小数点以下は
切り捨てられて月の値が決められる。
175.DATE関数とROW関数で、毎月の10日と20日の日付行を作成する。

上図のセルB16の「平成15.3.20」は文字列であり、[セルの書式設定]では表示形式を変更
出来ない。そこで、セルC16の様に、「SUBSTITUTE(B16,"平成","H")*1」と入力すると
数値に変換されて、[セルの書式設定]で表示形式が自由に変更できるようになる。
これは、「平成」と言う文字を、「H]に書き換えて「*1」する事によって、数値に変換され
「表示形式」が変更可能になるのである。
尚、「平成」は「H」、「昭和」は「S」、「令和」は「R」と書き換える。
174.SUBSTITUTE関数で、文字列を日付表示に変換する。

上図のセルB14の様に、日付を区切りを入れないで入力した、数字列を日付表示に変換する。
セルC14に、「TEXT(B14,"0000-00-00")*1」と入力すると、日付表示に変換できた。
TEXT関数の「表示形式」に、ダブルクオーテイションで囲んで指定すると日付にできる。
尚、後部の「*1」を省くと「2022-01-06」となり、文字列になるので「*1」を記入する
事によって、数値になり日付シリアル値が求められる。
尚、「"0000-00-00"」と入力しないとエラーになる。シリアル値に変換されたら、表示形式は
自由に変更できる。
TEXT関数の引数は、「=TEXT(値,表示形式)」で数値で指定した書式の文字列に変換する。
173.TEXT関数で、入力した数字を日付表示にする。
上図のセルC11に、「=DATEVALUE(B11)」と入力すると、B11セルの日付データのシリアル値が
表示される。表示されない場合は、[セルの書式設定]で[標準]か[数値]にするとよい。

尚、シリアル値のセルは、[セルの書式設定]の[日付]で自由に表示形式を選択できる。
172.DATEVALUE関数で、文字入力した日付を日付データに変換する。

上図のセルC9に、「=DATE(2024,5,10」と入力すると、「45422」と表示された。
これが、日付に対するシリアル値である。
DATE関数の引数は、「=DATE(年,月,日)」でセル値は指定できない。
171.DATE関数で、日付表示から日付データのシリアル値を求める。
上図のセルB9に、「=TODAY( )」と入力すると、今日の日付が表示される。
表示方法は、[セルの書式設定]で変更すればよい。
170.TODAY関数で、今日の日付を表示する。
上図のセルC6に、「=IF(WEEKDAY(B6,2)>5,"休",TEXT(B6,"aaa"))」と入力すると、「休」と
表示された。

WEEKDAY関数の引数は、「=WEEKDAY(日付,種類)」と入力すると、セルD3の様に「3」と
曜日に対する番号が表示される。「日付」はセル値でも「"5/1"」と直接日付でもよい。

WEEKDAY関数の第二引数の[種類]は、上図の位置まで入力すると表示される。
ここで使用している「2」は、[月曜日が1で日曜日が7]と返される。
だから、「=IF(WEEKDAY(B6,2)>5,"休",TEXT(B6,"aaa"))」の式では、「土曜日は6」
「日曜日は7」と返されるので「>5」となり、「休」と表示される。「平日」なら「aaa」で
曜日が表示される。
169.IF関数、WEEKDAY関数とTEXT関数で、土日を曜日ではなく休日と表示する。

上図のように、B3セルに月日だけを表示するには、[セルの書式設定]で[日付]の[種類]を選択
すれば、「5/1」と表示できる。

上図のセルC3の様に、日付の曜日を表示するには、「日付」のセルを指定し[セルの書式設定]
で[ユーザー定義]の[種類]に[aaa]と記入すれば、指定の曜日を表示することができる。
168.日付から曜日を表示する。
X.日付と時刻計算
この章では、日付と時刻計算に関する処理に使用する、関数の使い方を解説します。
関数の実用事例と引数の解説
日付と時刻計算に使用する関数の解説
実際に使っているシステムの内容で解説
EXCEL VBA 講座