LoginSignup
2
0

More than 3 years have passed since last update.

@Q11Q

[EXCELでは論文を書けない]エクセルで閏年を含めた日数計算を簡単に行う方法 How to calculate Date Diff Inclued Leap Year with Excel

Last updated at Posted at 2017-10-21

Excelでは論文を書けないシリーズ　第二回

前回、衝撃を与えた記事 EXCELでは論文を書けない　計算が正しくないから に引き続き、Excelが苦手とする計算が日数計算です。

確かにDateDifがある

日数計算にはDATEDIF 関数
DATEDIF(開始日,終了日,単位)

警告: Excel では、Lotus 1-2-3 の古いブックをサポートするために、DATEDIF 関数が用意されています。DATEDIF 関数では、特定のシナリオで誤った計算結果を返すことがあります。詳細については、この記事の既知の問題のセクションを参照してください。

既知の問題

"MD" 引数は、負の値、ゼロ、または不正確な結果を返すことがあります。1 か月が過ぎた後で、残りの日数を計算するには、こちらの回避策をお試しください。

DateDifは日付が文字列でなければならない。

開始日 期間の最初の日付または開始日を表します。 日付の入力には、引用符で囲まれた文字列 (例: "2001/1/30")、シリアル値 (例: 36921、1900 年日付システムでは 2001/1/30 を表します)、他の数式または関数の結果 (例: DATEVALUE("2001/1/30")) を使うことができます。
このため、表示形式を日付として内部で日付として扱われているセルの値は一度文字列に変換しないとエラーになる。

EXCELで数値を文字列に変える関数はないでしょうか

たとえば、セルA1に元の数値が入力されている場合
=TEXT(A1,"@") で文字列となります。
しかし、元のセルが空白の場合は 0(ゼロ)となり不都合が生じます。
そのため、 =TEXT(A1&"","@") とします。

Excel 2013 (win)ではDays関数がある

DAYS 関数
適用対象: Excel 2016 Excel 2013 Excel 2016 for Mac
Excel for Mac 2011
Excel Online
Excel for iPad Excel for iPhone Excel for Android タブレット
Excel Mobile Excel for Android
説明
2 つの日付間の日数を返します。
書式
DAYS(終了日, 開始日)

DAYS 関数の書式には、次の引数があります。
終了日 必ず指定します。 開始日と終了日を指定し、その間の日数を求めます。
開始日 必ず指定します。開始日と終了日を指定し、その間の日数を求めます。
注: Excel では、日付を計算に使用できるように一連のシリアル番号として保存します。 既定では、1900 年 1 月 1 日がシリアル値 1 として保存され、2008 年 1 月 1 日は 1900 年 1 月 1 日から 39447 日後に当たるので、シリアル値は 39448 になります。
解説
日付の引数が両方とも数値の場合、終了日と開始日の間の日数を計算します。
日付の引数のいずれかが文字列の場合は、その引数は DATEVALUE(日付文字列) として処理され、タイム コンポーネントの代わりに整数の日付が返されます。
日付の引数が有効な日付の範囲外の数値である場合、エラー値 #NUM! が返されます。
日付の引数が有効な日付として解析できない文字列である場合、エラー値 #VALUE! が返されます。

Days関数は閏年 Leap Yearは計算できない。

しかし、このDays関数も閏年は2016/1/1 2016/12/31とすると365日になる。
2016/1/1 2016/1/2と入力すると1日が返り、同日だとゼロになる。つまりこのDays関数は開始日か終了日を不算入にしているのだ。
このため、12/31日では365日として計算してしまう。
=DAYS("2017/1/1","2016/1/1")または=DAYS("2016/12/31","2016/1/1")+1としなければ366日という結果が返らず、閏年なのかわからない。(補足 公式のDays関数の解説では月日年=DAYS("2017/1/1","2016/1/1")は=DAYS("1/1/2017","1/1/2016")としているが、年月日表記で問題ない。また公式サイトの
月日2桁年という表記はエラーになる。たとえば=DAYS("12/31/16","1/1/16")はエラーになる)
よってこれも日数計算で閏年を認識するのが難しい。

「開始日を1日目としてカウントをはじめ、終了日は算入しない関数」だといえる。

Days関数は初日不算入関数

Days関数ヘルプ:日付の引数が両方とも数値の場合、終了日と開始日の間の日数を計算します。

とあり
=DAYS(31,29)
とすると
2
が返ってくる。つまり初日不算入だとわかる。これは方落とし(計算開始日か終了日のいずれか1日を入れる)ということになる。
日本の民法は初日不算入が原則であるが、英米法においても初日不算入が原則。
また
https://www.hicareer.jp/trans/houritsu2/1289.html
によると、
From toの表記の場合Fromの日付は不算入という判例がある。しかし9月1日からというときは参入されている場合もあり、あいまい。
https://www.mkikuchi-law.com/article/15864166.html
このため契約書において初日が算入される場合、From(include)などと算入されていることが明記される。
https://www.hicareer.jp/trans/houritsu2/1289.html
もしくは With / On が用いられる。
日本においては方入になる。
なお、上記サイトで
both days exclusiveが翻訳できないとしているが、それは誤りで、これにあたる表現は両落としである。

DAYS 関数の #VALUE! エラーを修正する方法

問題:引数が有効な日付を参照していない
June 22 2000 などのテキスト形式の日付が引数に含まれている場合、Excel は値を有効な日付として解釈できず、#VALUE! エラーをスローします。
注: 21-Oct-2006 などのテキスト形式の日付は関数で受け入れられ、#VALUE! エラーは発生しません。
解決方法:値を 06/22/2000 などの有効な日付に変更し、数式をもう一度テストします。
問題:システムの日付と時刻の設定がセルの日付と同期していない。
システムが mm/dd/yy 日付設定に従っており、数式が dd/mm/yyyy などの別の設定に従っている場合、#VALUE! エラーが表示されます。
解決方法:システムの日付と時刻の設定を調べ、数式で参照される日付の形式と一致していることを確認してください。必要に応じて、数式を更新して正しい形式になるようにしてください。

公式の解説ではわかりづらい問題:"4桁年/月/日"、"月/日/4桁年"はエラーにならないが、Days関数のマイクロソフトの解説に掲載されている"月/日/2桁年"表記はエラーになる。
image.png
解決方法:日本の場合システム日付はyyyy/mm/dd形式に従っておりmm/dd/yyyy形式が許容されている。マイクロソフトの本家本元のシステム設定は北米仕様なのでmm/dd/yyになっている。このため、北米仕様の表記ではエラーになる。日付の表記をyyyy/mm/ddに変えるか、DATE(yyyy,m,d)を用いて表記する。

NETWORKDAYS.INTL 関数

NETWORKDAYS.INTL 関数
　Excel 2003までは、「分析ツール」というアドインを組み込んで使用する。

週末の曜日とその日数を示すパラメーターを使用して、2 つの日付の間の稼働日数を返します。 週末および休日として指定した日はすべて、稼働日と見なされません。
書式 NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])
1 または省略 土曜日と日曜日
週末の文字列値は 7 文字で、文字列内の各文字は月曜日から始まる各曜日を表します。 1 は非稼働日を表し、0 は稼働日を表します。 文字列に使用できる文字は、1 および 0 のみです。 1111111 と指定すると、常に 0 が返されます。
たとえば、0000011 と指定すると土曜日と日曜日が週末になります。
休日 省略可能です。 稼働日の予定表から除外する日付のセットです。 休日の日付は、休日を表す日付が含まれたセル範囲またはシリアル値の配列定数として指定できます。 休日の日付またはシリアル値は、任意の順序で指定できます。(ここは設定しないこと)

ということは0000000 0を７つにするとすべて平日になる。

行列 A B C
1 2016/1/1 2016/12/31 =NETWORKDAYS.INTL(A1,B1,"0000000")

結果、366 という値が返る。

休日の設定の仕方

まずシートを新たに作る
シート名を
Holiday
とする。
日付をひたすら入力する。
日付を入れた範囲に名前を付ける(数式→名前の管理→新規作成) とりあえずYasumiとする
この日付は重複しても問題がないらしい。
また、週末として設定した日と重複しても問題がない
とりあえずHolidayシートA列に
2016/12/29
2016/12/30
2016/12/31
2017/1/1
2017/1/2

=NETWORKDAYS.INTL(A1,B1,1,Yasumi)
とすると答えは259

追記(2019/10/06)

この休日というか祝祭日の設定するシートに関しては
Excel VBA 2018年以降の日本の祝日を作り出して稼働日、営業日数を NetworkDays.INTL Workdays.INTLで計算させるマクロ
で大体できました。ただしA列ではなくB列になります。

VBA (EXCEL)

上記説明したことを一気に表示する。
Excelを起動し、Sheet1という名前のワークシートが1枚だけある状態で、次のVBAをコピペし、Runさせる。

Holidayset
Sub Holidayset()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets(1)
Dim wH As Worksheet
For Each wH In wb.Worksheets
Application.DisplayAlerts = False
If wH.Name = "Holiday" Then wH.Delete
Application.DisplayAlerts = True
Next
ws.Select
ws.UsedRange.Select
Selection.Clear
ws.Range("A1") = "2016/1/1"
ws.Range("B1") = "2016/12/31"
ws.Range("C1").Formula = "=" & "NETWORKDAYS.INTL(A1,B1,""0000000"")"
Set wH = wb.Worksheets.Add(, After:=wb.Worksheets(1))
wH.Name = "Holiday"
ActiveWorkbook.Worksheets("Holiday").Range("A1").Value = "2016/12/29"
ActiveWorkbook.Worksheets("Holiday").Range("A2").Value = "2016/12/30"
ActiveWorkbook.Worksheets("Holiday").Range("A3").Value = "2016/12/31"
ActiveWorkbook.Worksheets("Holiday").Range("A4").Value = "2017/1/1"
ActiveWorkbook.Worksheets("Holiday").Range("A5").Value = "2017/1/2"

ActiveWorkbook.Worksheets("Holiday").Names.Add Name:="Yasumi", RefersToR1C1 _
:="=Holiday!R1C1:R5C1"
ActiveWorkbook.Worksheets("Holiday").Names("Yasumi").Comment = ""
ws.Range("D1").Formula = "=" & "NETWORKDAYS.INTL(A1,B1,1,Holiday!yasumi)"
ws.Range("E1").Formula = "=" & "NETWORKDAYS.INTL(A1,B1,""0000000"",Holiday!yasumi)"
wb.Worksheets("Sheet1").Activate

    Range("D3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=DATEDIF(TEXT(R[-2]C[-3]&"""",""@""),TEXT(R[-2]C[-2]&"""",""@""),""D"")"
    Range("D4").Select
    Selection.ClearContents
    Range("D4").Select
    ActiveCell.FormulaR1C1 = _
        "DATEDIF(TEXT(A1&"""",""@""),TEXT(B1&"""",""@""),""D"")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "12/31/2016"
    Range("A1:B1").Select
    Selection.Copy
    Range("A5").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1/1/2016"
    Range("D3:D4").Select
    Selection.Copy
    Range("E3").Select
    ActiveSheet.Paste
    Range("D3:D4").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E3").Select
    ActiveCell.FormulaR1C1 = _
        "=DATEDIF(TEXT(R[-2]C[-4]&"""",""@""),TEXT(R[-2]C[-3]&"""",""@""),""D"")"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=DAYS360(R[-2]C[-3],R[-2]C[-2],FALSE)"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "DAYS360(A1,B1,FALSE)"
    Range("C1:E4").Select
    Selection.Copy
    Range("C5").Select
    ActiveSheet.Paste
    Range("C6").Select
    Application.CutCopyMode = False
    Range("C6:E8").Select
    Selection.Replace What:="A1", Replacement:="A5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Find(What:="B1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, MatchByte:=False, SearchFormat:=False).Activate
    Selection.Replace What:="B1", Replacement:="B5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A5:E8").Select
    Selection.Copy
    Range("A9").Select
    ActiveSheet.Paste
    Range("B9").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1/2/2016"
    Range("C10:E12").Select
    Selection.Replace What:="B5", Replacement:="B9", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="A5", Replacement:="A9", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B10").Select
    ActiveWindow.ScrollColumn = 1
    Range("C5").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("C1:C2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C5:C6").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C9:C10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A1,B1,""0000000"")"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A1,B1,1,Holiday!Yasumi)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A1,B1,""0000000"",Holiday!Yasumi)"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A5,B5,""0000000"")"
    Range("C10").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A9,B9,""0000000"")"
    Range("D10").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("D10").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A9,B9,1,Holiday!Yasumi)"
    Range("E10").Select
    ActiveCell.FormulaR1C1 = "NETWORKDAYS.INTL(A9,B9,""0000000"",Holiday!Yasumi)"
    Range("F10").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=DAYS(R[-2]C[-1],R[-2]C[-2])"
    Range("C3").Select
    Selection.Copy
    Range("C7").Select
    ActiveSheet.Paste
    Range("C11").Select
    ActiveSheet.Paste
    Range("C4").Select
    Application.CutCopyMode = False
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "DAYS(B1,A1)"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "DAYS(B5,A5)"
    Range("C12").Select
    ActiveCell.FormulaR1C1 = "DAYS(B9,A9)"
    Range("D12").Select
End Sub

image.png

閏年を含めた日数計算を簡単に行う方法

以上から、次のように結論付けられる。

1.　データ型を「日付」に設定したセルの日付を開始日、終了日として
2.　Networkdays.Intlで「休日なし、祝日なし」に設定すると日数は正確に計算できる。
3.　ただし開始日、終了日とも1日として数える。初日不算入などは-1を加える。
4.　開始日と終了日が同日なら1日としてカウントする
5.　開始日をA1 2016/1/1 終了日をB1 2016/12/31を入力し、C1に次の関数を入力する。
=NETWORKDAYS.INTL(A1,B1,"0000000")

参考

日付/時刻関数 - Office Kitami 北見あきこのホームページ
こちらが日付関数に関してさらに細かい解説がある。

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0