EXCEL VBA 他 時間計算 時間シリアル 十進数変換法

目次

• 和
• 差
• excelはマイナスの時間を扱えない
• 式内部の時間
• 文字から時間シリアル
• 換算、変換のテクニック
• 表示形式のテクニック
• 2017/1/24更新しました(C43の式が間違っていたののほかA50以下の解説追加、参考リンクの追加)
• 2017/1/25更新しました(解説、換算式の追加)2017/1/25更新(負の時間、式内の時間)

和

• セルに入っている数が整数かシリアル値かで違う(C6)
• 時間(hour)を表す整数なら24で割って足す(C6)
• 時間(hour)を表すシリアル値ならそのまま足す。(C7)
• ここで演算誤差が発生することが理解できる。
• 見た目が似ていても本当の値は違う。
• 整数値にする場合、小数点以下を表示しないがドットが残る表示形式か、条件付き書式で区別できる

差

何時間か(差)
時間を長整数型にするE2
引いて*24でRoundする

excelはマイナスの時間を扱えない

• マイナスになる計算をさせるとエラーになる(C58)
• 1904オプションを有効にすると解決できるが、普通はやらない。なおこのオプションを有効にしてもマイナスの時間を入力することはできない。
• 絶対値で計算させるとできる。ただしセルの内部は正のシリアル値になる
• マイナスの時間はオプションを変えても有効に入力すらできない。

セル内の数式内部の時間(64行目から67行)

式内部での表記は二重引用符で囲み”09:00"or”09:00:00"と書く

そのうえで*1としないと文字列として扱われる。

'=IF(A66>="9:00:00"*1,TRUE,FALSE)

換算、変換のテクニック

関数を使う

TimeValue関数を使う

Time関数を使う

=Time(16.50,21)という形で1日にのうちの時刻を示すことができる。

定数で換算する

十進数から時間(シリアル)にするには以下の数で割る(B38~B40)

時間にするのが24
分にするのが3600
秒にするのが86400

十進数から時間(シリアル)にするには以下の「数」を乗じてもよい(C38~C40)

時間にするには("01:00:00")をかける
分にするには("00:01:00")をかける
秒にするには("00:00:01")をかける

文字から時間、十進数とシリアル値の変換は表示形式や定数で乗除して行う

時間から時分秒に換算

Hourなどの関数を使う。ただしこの関数はそれぞれの最大値が24,60,60を超えてはならない。
さらにそれを換算定数で乗除して十進数と時間シリアル値の換算ができる
時間にするのが24　
分にするのが3600
秒にするのが86400
または時間は十進数を"01:00:00"で割ってもよい(C51)
=1/”00:01:00”とすると1分のシリアル値が求められる

1600分は何時間か

=INT(A18/24)&":"& MOD(A18,60)
24で割った数が時間 60で割った余りが分

24時間を超えるか超えないかで違うこと

和が24時間を超える場合は表示形式を変える

With ws.Range("\$C$13")
.Formula = "=SUM(C10:C12)": .NumberFormat = "[h]:\mm:ss"
End With

表示形式で小数点以下を表示するかしないかがかえられる

ユーザー定義(G3)ドット(小数点が残ってしまう)
条件付き書式(H3)小数点は残らないが、設定方法が複雑

VBAのコード

基本的な変換

文字列から時間へ

CDateで囲む

CDateで囲むと時間になる
Cdate(“01:00:00”)

リテラルで時間だけ囲む

リテラルで時間だけ囲むと時間になる。
#01:00:00 PM# #13:00:00# どちらでもよい
#01:00:00 PM# = Cdate(“13:00:00”)
1日うちの何時かはTimeValue関数を使う
TimeValue(“01:00:00”)
TimeValue("01:22:01 PM")

TimeSirialを使う

TimeSerial(2,200, 300)
-32,768 ～ 32,767の数を使える
合計がマイナスにならない限り、マイナスの値、マイナスになる数式も使える。24時間を超えると0元に戻る。
?TimeSerial(8,-200, 32000)
13:33:20

解説コード(2018/1/25更新)

TimeCalculateExcel

Sub TimeCalculateExcel()
’For Excel 2013 Later
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = ActiveSheet
Dim DT1 As Date, DT2 As Date, DT3 As Date
Dim ibyte As Byte, iIn As Integer, iPtr As LongPtr, iLong As Long, iDbl As Double, iSgl As Single, iDec As Variant, iCur As Currency
Dim buf As String
Dim arFormula, arValue

Columns("A:A").ColumnWidth = 27.75
Columns("B:B").ColumnWidth = 27.75
Columns("C:C").ColumnWidth = 11.25
Columns("D:D").ColumnWidth = 11.38
Columns("E:E").ColumnWidth = 37.88
Columns("F:F").ColumnWidth = 26.75
Columns("G:G").ColumnWidth = 8.63
Columns("I:I").ColumnWidth = 9.63
Columns("H:H").ColumnWidth = 10.38
Rows("12:27").RowHeight = 27
With ws.Range("$A$1")
.Value = "開始"
.NumberFormat = "General"
End With
With ws.Range("$B$1")
.Value = "終了"
.NumberFormat = "General"
End With
With ws.Range("$C$1")
.Value = "時間"
.NumberFormat = "General"
End With
With ws.Range("$D$1")
.Value = "時間表示形式変更"
.NumberFormat = "General"
End With

With ws.Range("$E$1")
.Value = "十進数*24"
.NumberFormat = "General"
End With

With ws.Range("$F$1")
.Value = "十進数*24のround3"
.NumberFormat = "General"
End With

With ws.Range("$G$1")
.Value = "数式"
.NumberFormat = "General"
End With

With ws.Range("$H$1")
.Value = "分単位*60Int"
.NumberFormat = "General"
End With
With ws.Range("$I$1")
.Value = "参考リンク"
.NumberFormat = "General"
End With
With ws.Range("$A$2")
.Value = "0.625"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$2")
.Value = "0.673611111111111"
.NumberFormat = "h:mm"
End With

With ws.Range("$I$2")
.Value = "Via:http://www.relief.jp/docs/000897.html"
.NumberFormat = "General"
End With

With ws.Range("$A$3")
.Value = "0.625"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$3")
.Value = "0.673611111111111"
.NumberFormat = "h:mm"
End With
With ws.Range("$D$3")
.Value = "←時間表示形式h"
.NumberFormat = "General"
End With

With ws.Range("$I$3")
.Value = "Via:[2 つの時刻間の差分を計算する]（https://support.office.com/ja-jp/article/2-%E3%81%A4%E3%81%AE%E6%99%82%E5%88%BB%E9%96%93%E3%81%AE%E5%B7%AE%E5%88%86%E3%82%92%E8%A8%88%E7%AE%97%E3%81%99%E3%82%8B-e1c78778-749b-49a3-b13e-737715505ff6)"
.NumberFormat = "General"
End With

With ws.Range("$A$4")
.Value = "0.625"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$4")
.Value = "16:00"
.NumberFormat = "h:mm"
End With
With ws.Range("$D$4")
.Value = "=TEXT(B4-A4,""h:m:ss"")"
.NumberFormat = "General"
End With

With ws.Range("$E$4")
.Value = "文字列になってしまうがTimeValueで戻せる→"
.NumberFormat = "General"
End With

With ws.Range("$I$4")
.Value = "VIA:[「8:15」を計算できる数値「8.25」に変換したい](https://www.wanichan.com/pc/excel/2003/05/167.html) "
.NumberFormat = "General"
End With

With ws.Range("$A$5")
.Value = "○1時間を足す"
.NumberFormat = "h:mm"
.Font.Bold = True
End With
With ws.Range("$E$5")
.Value = "TimeValue関数のに似ているのが、何時かを直接かけるTIME(時,分,秒)であるTime(16,21,20)は→"
End With
With ws.Range("$F$5")
.Formula = "=Time(16,51,20)"
.NumberFormat = "h:mm:ss"
End With

With ws.Range("$A$25")
.Value = "○時間の差の計算"
.Font.Bold = True
End With
With ws.Range("$C$26")
.Value = "○日付+時刻または時刻のみで最後にスペースaかスペースpで午前か午後を区別して入力できる。24時制でもよい"
.NumberFormat = "h:mm"
End With

With ws.Range("$A$6")
.Value = "0.625"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$6")
.Value = "1"
.NumberFormat = "General"
End With

With ws.Range("$D$6")
.Value = "=A6+(B6/24)"
.NumberFormat = "General"
End With

With ws.Range("$E$6")
.Value = "数字の１なので24で割って足す"
.NumberFormat = "General"
End With

With ws.Range("$I$6")
.Value = "VIA:[時刻データ (0:30) を、計算できる数値 (0.5) に変換するには ](https://support.microsoft.com/ja-jp/help/881211)"
.NumberFormat = "General"
End With

With ws.Range("$A$7")
.Value = "0.625"
.NumberFormat = "h:mm"
End With
With ws.Range("$D$7")
.Value = "=A7+B7"
.NumberFormat = "General"
End With

With ws.Range("$E$7")
.Value = "1時間(シリアル値)なのでそのまま足す"
.NumberFormat = "General"
End With
With ws.Range("$I$5")
.Value = "[Via:時間の計算 - Excelの学校 - 内容は古い]( http://www.excel.studio-kazu.jp/lib/e2h/e2h.html)"
.NumberFormat = "General"
End With
With ws.Range("$I$7")
.Value = "[Via:エクセル 2016 時間計算する方法](https://www.tipsfound.com/excel/02207)"
.NumberFormat = "General"
End With
With ws.Range("$I$7")
.Value = "[Via:OfficeTanaka](http://officetanaka.net/excel/function/tips/tips68.htm)"
.NumberFormat = "General"
End With
With ws.Range("$A$8")
.Font.Bold = True
.Value = "○時間の加法合計(24時間を超える場合)"
.NumberFormat = "General"

End With

With ws.Range("$A$10")
.Value = "0"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$10")
.Value = "0.3125"
.NumberFormat = "h:mm"
End With
With ws.Range("$A$11")
.Value = "0"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$11")
.Value = "0.701388888888889"
.NumberFormat = "h:mm"
End With
With ws.Range("$A$12")
.Value = "0"
.NumberFormat = "h:mm"
End With
With ws.Range("$B$12")
.Value = "0.9375"
.NumberFormat = "h:mm"
End With
With ws.Range("$D$13")
.Value = "表示形式を変更して合計"
.NumberFormat = "General"
End With

With ws.Range("$A$14")
.Value = "○Hour,Minute,Second関数で分解し、TimeValue関数を使う"
.NumberFormat = "General"
End With

With ws.Range("$B$15")
.Value = "Hour"
.NumberFormat = "General"
End With

With ws.Range("$C$15")
.Value = "Minute"
.NumberFormat = "General"
End With

With ws.Range("$D$15")
.Value = "Second"
.NumberFormat = "General"
End With

With ws.Range("$A$16")
.Value = "0.652916666666667"
.NumberFormat = "h:mm:ss"
End With
With ws.Range("$E$16")
.Value = "=TIMEVALUE(""15:40:12"")"
.NumberFormat = "General"
End With

With ws.Range("$A$17")
.Value = "○分を時：分に変える"
.NumberFormat = "h:mm:ss"
End With
With ws.Range("$A$18")
.Value = "1600"
.NumberFormat = "General"
End With

With ws.Range("$C$18")
.Value = "=INT(A18/24)&"":""& MOD(A18,60)"
.NumberFormat = "General"
End With

With ws.Range("$A$19")
.Value = "○24時間を超える時間の足し算"
.NumberFormat = "General"
End With
With ws.Range("$A$38")
.Value = 4
End With
With ws.Range("$C$20")
.Value = "0.28125"
.NumberFormat = "h:mm"
End With
With ws.Range("$C$21")
.Value = "0.159722222222222"
.NumberFormat = "h:mm"
End With
With ws.Range("$C$22")
.Value = "0.833333333333333"
.NumberFormat = "h:mm"
End With
With ws.Range("$D$24")
.Value = "表示形式"
.NumberFormat = "General"
End With

'''式
With ws.Range("$D$2")
.Formula = "=C2":
.NumberFormat = "General"
End With
With ws.Range("$E$2")
.Formula = "=D2*24"
.NumberFormat = "0.00000000000000000"
End With
With ws.Range("$F$2")
.Formula = "ROUND(E2,3)"
.NumberFormat = "0.00000000000000000"
End With
With ws.Range("$G$2")
.Formula = "=(INT(((ABS(B2-A2))*24)*10^6+0.1)/(10^6))*SIGN(B2-A2)"
.NumberFormat = "General"
End With
With ws.Range("$H$2")
.Formula = "=(INT((ABS(G2)*60)*100+0.1)/100)*SIGN(G2)"
.NumberFormat = "General"
End With
With ws.Range("$C$3")
.Formula = "=B3-A3": .NumberFormat = "h"
End With
With ws.Range("$C$4")
.Formula = "=TEXT(B4-A4,""h:m:ss"")": .NumberFormat = "General"
End With
With ws.Range("$F$4")
.Formula = "=TIMEVALUE(C4)": .NumberFormat = "General"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=""if(F4-inf(f4)=0,""""#,##0"""",""""#,##0.0#"""")"""
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).StopIfTrue = True
End With
With ws.Range("$C$6")
.Formula = "=A6+(B6/24)": .NumberFormat = "[$-F400]h:mm:ss AM/PM"
End With
With ws.Range("$B$7")
.Formula = "=" & "0.0416666666666667": .NumberFormat = "h:mm"
End With
With ws.Range("$C$7")
.Formula = "=A7+B7": .NumberFormat = "h:mm"
End With
With ws.Range("$C$10")
.Formula = "=B10-A10": .NumberFormat = "h:mm"
End With
With ws.Range("$C$11")
.Formula = "=B11-A11": .NumberFormat = "h:mm"
End With
With ws.Range("$C$12")
.Formula = "=B12-A12": .NumberFormat = "h:mm"
End With
With ws.Range("$C$13")
.Formula = "=SUM(C10:C12)": .NumberFormat = "[h]:mm:ss"
End With
With ws.Range("$B$16")
.Formula = "=HOUR(A16)": .NumberFormat = "General"
End With
With ws.Range("$C$16")
.Formula = "=MINUTE(A16)": .NumberFormat = "General"
End With
With ws.Range("$D$16")
.Formula = "=SECOND(A16)": .NumberFormat = "General"
End With
With ws.Range("$F$16")
.Formula = "TIMEVALUE(""15:40:12"")": .NumberFormat = "General"
End With
With ws.Range("$B$18")
.Formula = "=INT(A18/24)&"":""& MOD(A18,60)": .NumberFormat = "General"
End With
With ws.Range("$C$23")
.Formula = "=SUM(C20:C22)": .NumberFormat = "h:mm"
End With
With ws.Range("$C$24")
.Formula = "=SUM(C20:C22)": .NumberFormat = "[h]:mm:ss;@@@"
End With
With ws.Range("$A$26")
.Formula = """2007/6/9 10:35:00 a""": .NumberFormat = "General"
End With
With ws.Range("$B$26")
.Formula = """2007/6/9 3:30:00 p""": .NumberFormat = "General"
End With
With ws.Range("$A$27")
.Formula = "=" & "39242.4409722222": .NumberFormat = "yyyy/m/d h:mm;True;False;"
End With
With ws.Range("$B$27")
.Formula = "=B27-A27"
.NumberFormat = "yyyy/m/d h:mm"
.ShrinkToFit = True
End With
With ws.Range("$C$27")
.Formula = "=B27-A27": .NumberFormat = "h"
End With
With ws.Range("$C$28")
.Formula = "=B27-A27": .NumberFormat = "h:mm"
End With
With ws.Range("$C$29")
.Formula = "=TEXT(B27-A27,""h:mm"")": .NumberFormat = "General"
End With
With ws.Range("$D$29")
.Value = "'=TEXT(B27-A27,""h:mm"")"
End With
With ws.Range("$C$30")
.Formula = "=TEXT(B27-A27,""h:mm:ss"")": .NumberFormat = "General"
End With
With ws.Range("$D$27")
.Value = "'=B27-A27 で表示形式がhのみのため時間しか表示されない"
End With
With ws.Range("$D$28")
.Value = "'=B27-A27 で表示形式をh:mmとしているので、時間が表示される"
End With
With ws.Range("$A$37")
.Value = "〇十進数の4を4時間に変換するには4を24で割って表示形式を変える。"
End With
With ws.Range("$D$29")
.Value = "''=TEXT(B27-A27,""h:mm""):ただしテキストになってしまう"
End With
With ws.Range("$D$30")
.Value = "''=TEXT(B27-A27,""h:mm:ss""):ただしテキストになってしまう"
End With
With ws.Range("$C$31")
.Formula = "=(B27-A27)*1440": .NumberFormat = "General"
End With
With ws.Range("$D$31")
.Value = "''=(B27-A27)*1440時間のシリアル値の時間の差は1440倍することで、分単位に換算できる"
End With
With ws.Range("$C$32")
.Formula = "=(B27-A27)*86400": .NumberFormat = "General"
End With
With ws.Range("$D$32")
.Value = "''=(B27-A27)*86400時間のシリアル値の時間の差は86400倍することで、秒単位に換算できる"
End With
With ws.Range("$C$33")
.Formula = "=HOUR(B27-C27)": .NumberFormat = "General"
End With
With ws.Range("$C$34")
.Formula = "=MINUTE(B27-A27)": .NumberFormat = "General"
End With
With ws.Range("$C$35")
.Formula = "=SECOND(B27-A27)": .NumberFormat = "General"
End With
With ws.Range("$B$38")
.Formula = "=A38/24": .NumberFormat = "[$-F400]h:mm:ss AM/PM"
End With
With ws.Range("$B$43")
.Formula = "=(DAY(A43)*24+HOUR(A43))+(MINUTE(A43)/60)": .NumberFormat = "General"
End With
With ws.Range("$B$44")
.Formula = "=(DAY(A44)*24+HOUR(A44))+(MINUTE(A44)/60)": .NumberFormat = "General"
End With
With ws.Range("$B$45")
.Formula = "=(DAY(A45)*24+HOUR(A45))+(MINUTE(A45)/60)": .NumberFormat = "General"
End With
With ws.Range("$A$47")
.Formula = "=SUM(A43:A45)": .NumberFormat = "h:mm"
End With
With ws.Range("$B$47")
.Formula = "=SUM(B43:B45)": .NumberFormat = "General"
End With
With ws.Range("$A$48")
.Formula = "=A47": .NumberFormat = "[h]:mm:ss;@@@"
End With


With ws.Range("$B$53")
.Formula = "=(DAY(A53)*24+HOUR(A53))+(MINUTE(A53)/60)": .NumberFormat = "General"
End With

With ws.Range("$C$2")
.Formula = "=B2-A2":
.NumberFormat = "h:mm"
End With
With ws.Range("$D$2")
.Formula = "=C2":
.NumberFormat = "General"
End With
With ws.Range("$E$2")
.Formula = "=D2*24":
.NumberFormat = "0.00000000000000000"
End With
With ws.Range("$F$2")
.Formula = "ROUND(E2,3)":
.NumberFormat = "0.00000000000000000"
End With
With ws.Range("$G$2")
.Formula = "=(INT(((ABS(B2-A2))*24)*10^6+0.1)/(10^6))*SIGN(B2-A2)":
.NumberFormat = "General"
End With
With ws.Range("$H$2")
.Formula = "=(INT((ABS(G2)*60)*100+0.1)/100)*SIGN(G2)":
.NumberFormat = "General"
End With
With ws.Range("$C$3")
.Formula = "=B3-A3":
.NumberFormat = "h"
End With
With ws.Range("$C$4")
.Formula = "=TEXT(B4-A4,""h:m:ss"")":
.NumberFormat = "General"
End With
With ws.Range("G3")
.Value = "ドットが残り表示形式"
.NumberFormat = "General"
End With
With ws.Range("H3")
.Value = "条件付き書式"
.NumberFormat = "General"
End With
With ws.Range("$F$4")
.Formula = "=TIMEVALUE(C4)":
.NumberFormat = "General"
End With
With ws.Range("G4")
.Formula = "=F4*24":
.NumberFormat = "#,##0.#;[Red]-#,##0.#"
End With
With ws.Range("H4")
.Formula = "=F4*24":
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=""if(E4-inf(E4)=0,""""#,##0"""",""""#,##0.0#"""")"""
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).StopIfTrue = True
End With
With ws.Range("$C$6")
.Formula = "=A6+(B6/24)":
.NumberFormat = "[$-F400]h:mm:ss AM/PM"
End With
With ws.Range("$B$7")
.Formula = "=" & "0.0416666666666667"
.NumberFormat = "h:mm"
End With
With ws.Range("$C$7")
.Formula = "=A7+B7":
.NumberFormat = "h:mm"
End With
With ws.Range("$C$10")
.Formula = "=B10-A10":
.NumberFormat = "h:mm"
End With
With ws.Range("$C$11")
.Formula = "=B11-A11":
.NumberFormat = "h:mm"
End With
With ws.Range("$C$12")
.Formula = "=B12-A12"
.NumberFormat = "h:mm"
End With
With ws.Range("$C$13")
.Formula = "=SUM(C10:C12)":
.NumberFormat = "[h]:mm:ss"
End With
With ws.Range("$B$16")
.Formula = "=HOUR(A16)":
.NumberFormat = "General"
End With
With ws.Range("$C$16")
.Formula = "=MINUTE(A16)":
.NumberFormat = "General"
End With
With ws.Range("$D$16")
.Formula = "=SECOND(A16)":
.NumberFormat = "General"
End With
With ws.Range("$F$16")
.Formula = "=TIMEVALUE(""15:40:12"")":
.NumberFormat = "General"
End With
With ws.Range("$B$18")
.Formula = "=INT(A18/24)&"":""& MOD(A18,60)":
.NumberFormat = "General"
End With
With ws.Range("$C$23")
.Formula = "=SUM(C20:C22)":
.NumberFormat = "h:mm"
End With
With ws.Range("$C$24")
.Formula = "=SUM(C20:C22)":
.NumberFormat = "[h]:mm:ss;@@@"
End With
With ws.Range("$A$26")
.Formula = """2007/6/9 10:35:00 a"""
.NumberFormat = "General"
End With
With ws.Range("$B$26")
.Formula = """2007/6/9 3:30:00 p"""
.NumberFormat = "General"
End With
With ws.Range("$A$27")
.Formula = "=" & "39242.4409722222"
.NumberFormat = "yyyy/m/d h:mm"
.ShrinkToFit = True
End With
With ws.Range("$B$27")
.Formula = "=" & "39242.6458333333"
.NumberFormat = "yyyy/m/d h:mm"
.ShrinkToFit = True
End With
With ws.Range("$C$27")
.Formula = "=B27-A27":
.NumberFormat = "h"
End With
With ws.Range("$C$28")
.Formula = "=B27-A27":
.NumberFormat = "h:mm"
End With
With ws.Range("$C$29")
.Formula = "=TEXT(B27-A27,""h:mm"")":
.NumberFormat = "General"
End With
With ws.Range("$C$30")
.Formula = "=TEXT(B27-A27,""h:mm:ss"")":
.NumberFormat = "General"
End With
With ws.Range("$C$31")
.Formula = "=(B27-A27)*1440":
.NumberFormat = "General"
End With
With ws.Range("$C$32")
.Formula = "=(B27-A27)*86400":
.NumberFormat = "General"
End With
With ws.Range("$C$33")
.Formula = "=HOUR(B27-C27)":
.NumberFormat = "General"
End With
With ws.Range("$C$34")
.Formula = "=MINUTE(B27-A27)":
.NumberFormat = "General"
End With
With ws.Range("$C$35")
.Formula = "=SECOND(B27-A27)":
.NumberFormat = "General"
End With
With ws.Range("$B$38")
.Formula = "=A38/24":
.NumberFormat = "=[$-F400]h:mm:ss AM/PM"
End With
With ws.Range("$B$43")
.Formula = "=(DAY(A43)*24+HOUR(A43))+(MINUTE(A43)/60)":
.NumberFormat = "General"
End With
With ws.Range("$B$44")
.Formula = "=(DAY(A44)*24+HOUR(A44))+(MINUTE(A44)/60)":
.NumberFormat = "General"
End With
With ws.Range("$B$45")
.Formula = "=(DAY(A45)*24+HOUR(A45))+(MINUTE(A45)/60)":
.NumberFormat = "General"
End With
With ws.Range("$A$47")
.Formula = "=SUM(A43:A45)":
.NumberFormat = "h:mm"
End With
With ws.Range("$B$47")
.Formula = "=SUM(B43:B45)":
.NumberFormat = "General"
End With
With ws.Range("$A$48")
.Formula = "=A47":
.NumberFormat = "[h]:mm:ss;@@@"
End With
With ws.Range("$B$51")
.Formula = "=A51/""01:00:00""":
.NumberFormat = "General"
End With
With ws.Range("$B$52")
.Formula = "=(DAY(A52)*24+HOUR(A52))+(MINUTE(A52)/60)":
.NumberFormat = "General"
End With
With ws.Range("$B$53")
.Formula = "=(DAY(A53)*24+HOUR(A53))+(MINUTE(A53)/60)":
.NumberFormat = "General"
End With
With ws.Range("$A$55")
.Formula = "=SUM(A51:A53)":
.NumberFormat = "h:mm"
End With
With ws.Range("$B$55")
.Formula = "=SUM(B51:B53)":
.NumberFormat = "General"
End With
'---2018/01/24追加
With ws.Range("$A$19")
.Value = "○24時間を超える時間の合計その2"
.Font.Bold = True
.NumberFormat = "General"
End With
With ws.Range("$D$24")
.Value = "表示形式を[h]:mm:ss;@@@に変更して合計"
.NumberFormat = "General"
End With
With ws.Range("$D$13")
.Value = "表示形式を[h]:mm:ssに変更して合計"
.NumberFormat = "General"
End With
With ws.Range("$E$5")
.Value = "TimeValue関数と違い、TIME(時,分,秒)で直接時間を指定する関数。Time(16,21,20)→"
End With
With ws.Range("$A$17")
.Value = "○分を時：分に変える"
.NumberFormat = "h:mm:ss"
.Font.Bold = True
End With
With ws.Range("$C$18")
.Value = "=INT(A18/24)&"":""& MOD(A18,60)"
.NumberFormat = "General"
End With
With ws.Range("$D$18")
.Value = "'=INT(A18/60)&"":""& MOD(A18,60) 60で割って整数化したのが時間、余りが分"
.NumberFormat = "General"
End With
With ws.Range("A42")
.Value = "○整数部分が日数の十進数を時 分 秒に換算する"
.NumberFormat = "General"
.Font.Bold = True
End With
With ws.Range("A43")
.Value = 1.2384
.NumberFormat = "General"
End With
With ws.Range("D33:D35")
.Value = "'=HOUR(B27-C27)で時間が出る(24時間を超えると1に戻る)"
.NumberFormat = "General"
End With
ws.Range("D34").Value = "'=Minute(B27-C27)で分が出る(60分を超えると1に戻る)"
ws.Range("D35").Value = "''=Second(B27-C27)で分が出る(60秒を超えると1に戻る)"


With ws.Range("C42")
.Value = "整数をIntで出して、24倍する"
End With
With ws.Range("D42")
.Value = "Hour関数を使う"
.NumberFormat = "General"
End With
With ws.Range("E42")
.Value = "Minute関数/60"
.NumberFormat = "General"
End With
With ws.Range("F42")
.Value = "Second関数/86400"
.NumberFormat = "General"
End With
With ws.Range("D43")
.Formula = "=Hour(0.2384)"
End With
With ws.Range("B43")
.Formula = "=(INT(ABS(A43))*24+HOUR(ABS(A43))+(MINUTE(ABS(A43))/60)+(SECOND(ABS(A43))/86400))*SIGN(A43)"
.NumberFormat = "General"
End With
ws.Range("B43").Select
    Selection.Copy
    Range("B44:B45").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
With ws.Range("A44")
.Formula = "2.5"
.NumberFormat = "General"
End With
With ws.Range("A47")
.NumberFormat = "General"
End With
With ws.Range("C43")
.Formula = "=INT(A43)*24"
.NumberFormat = "General"
End With
With ws.Range("E43")
.Formula = "=MINUTE(0.2384)/60"
.NumberFormat = "General"
End With
With ws.Range("F43")
.Formula = "=SECOND(0.2384)/(24*60*60)"
.NumberFormat = "General"
End With
With ws.Range("C46")
.Value = "'日時間にしたいとき=INT(INT(B47)/24)整数をIntで出して、24で割り、日数分を引いてからHour関数を適用する。実際の式は符号に注意している。分、秒は各自作ってください。"
.NumberFormat = "General"
End With
With ws.Range("C48")
.Value = "'日"
.NumberFormat = "General"
.HorizontalAlignment = xlRight
End With
With ws.Range("E48")
.Value = "'時間"
.NumberFormat = "General"
.HorizontalAlignment = xlRight
End With
With ws.Range("C47")
.Formula = "=INT(INT(ABS(B47))/24)*SIGN(B47)"
.NumberFormat = "General"
End With
With ws.Range("D47")
.Formula = "=(ABS(B47)-24*C47)*SIGN(B47)"
.NumberFormat = "General"
End With
With ws.Range("E47")
.Formula = "=HOUR(ABS(D47))*SIGN(B47)"
.NumberFormat = "General"
End With
ws.Range("A50").Value = "〇十進数の日数を単純に時間に変える方法(24倍にしない方法) ": ws.Range("A50").Font.Bold = True
ws.Range("A51").Value = 1: ws.Range("A51").NumberFormat = "General"
ws.Range("A52").Value = 3: ws.Range("A51").NumberFormat = "General"
ws.Range("A53").Value = 6: ws.Range("A51").NumberFormat = "General"
ws.Range("C51").Value = "'=A51/""01:00:00""として1時間で割っても出てくる"
ws.Range("C52").Value = "'=A52*24としても出てくる"
ws.Range("B52").Formula = "=A52*24"
'2018/1/25
Range("A37").Select
Range("A37").Value = "〇十進数の4を4時間、4分間、4秒間に変える"
Selection.Font.Bold = True
Range("B38:C40").Select
Selection.NumberFormatLocal = "h:mm:ss"
Range("D38:D40").Select
Selection.NumberFormatLocal = "0.000000000000000_);[赤](0.000000000000000)"
Selection.ShrinkToFit = True
Range("D37").Value = "実数"
Range("C37").Value = "定数倍"
Range("E38").Value = "時間は24で割るか(""01:00:00"")をかける"
Range("E39").Value = "分は24*60で割るか(""00:01:00"")をかける"
Range("E40").Value = "秒は24*60*60で割るか(""00:00:01"")をかける"
Range("B38").FormulaR1C1 = "=RC[-1]/24"
Range("B39").FormulaR1C1 = "=RC[-1]/24/60"
Range("B40").FormulaR1C1 = "=RC[-1]/24/60/60"
Range("C38").Formula = "=A38*(""01:00:00"")"
Range("C39").Formula = "=A39*(""00:01:00"")"
Range("C40").Formula = "=A40*(""00:00:01"")"
Range("D38").Formula = "=C38"
Range("A38").Select
Selection.Copy
Range("A39:A40").Select
ActiveSheet.Paste
Range("D38").Select
Selection.Copy
Range("D39:D40").Select
ActiveSheet.Paste
'2018/1/27追加
     Range("A57").Value = "〇負の時間計算": Range("A57").Font.Bold = True
     Range("A58").FormulaR1C1 = "1:00:00"
    Range("B58").FormulaR1C1 = "23:00:00"
    Range("C58").FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("D58").FormulaR1C1 = "=RC[-3]-RC[-2]"
    Selection.NumberFormatLocal = "[h]mm:ss"
       Range("E58").FormulaR1C1 = _
        "=IF(RC[-1]>=0,TEXT(RC[-1],""[h]:mm:ss""),TEXT(ABS(RC[-1]),""-[h]:mm:ss""))"
    Range("C59").Value = _
        "'A58-B58とするとマイナスになり###で表示されず、h:mm:ss;[赤]-[h]:mm:ssと表示形式を変えてもだめ"
    Range("D29").FormulaR1C1 = _
        "''=TEXT(B27-A27,""h:mm""):ただしテキストになってしまう(中身は数字だが表示はテキスト)"
    Range("D30").FormulaR1C1 = _
        "''=TEXT(B27-A27,""h:mm:ss""):ただしテキストになってしまう(中身は数字だが表示はテキスト)"
    Range("A60").Value = "'-01:00:00と入力しようとしてもエラーになる。"
    Range("A61").Value = "1904オプションを設定しても同じ。"
    ActiveWorkbook.Date1904 = False
    Range("C60").Value = "オプションで1904年から計算するを有効にすると表示されるが通常はいじらない。(VBAのコードはActiveWorkbook.Date1904 = True)"
    Range("C61").Value = "E58のような式=IF(D58>=0,TEXT(D58,""[h]:mm:ss""),TEXT(ABS(D58),""-[h]:mm:ss""))ただし中身はマイナスではないのでセルの中身で符号を変えるだけの条件付き書式では赤くできない"
    Range("F58").FormulaR1C1 = _
        "=IF(RC[-1]>=0,TEXT(RC[-1],""[h]:mm:ss""),TEXT(ABS(RC[-1]),""-[h]:mm:ss""))"
    Range("I8").Value = "[VIA:エクセル関数の技：時刻・時間の計算に関する技 - エクセル関数の技：時刻・時間の計算に関する技](http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/jikan01.html)"
    Range("I9").Value = "[VIA:マイナスの時間計算で結果が「#####」- なんだ！カンタン！Excel塾](https://kokodane.com/2013_waza_059.htm)"
    Range("A63").Value = "〇式の中に式の中での時間の取り扱いに注意。9:00:00は二重引用符で囲み*1しないと時間シリアルにならない": Range("A63").Font.Bold = True
    Range("A64:A66").Value = "09:00:00": Range("A67").Value = "00:00:10":: Range("A64:A67").NumberFormatLocal = "hh:mm:ss"
    Range("C64").Value = "←A64に9時のシリアル値が入っている"
    Range("B65").Formula = "=IF(A65>=""9:00"",TRUE,FALSE)": Range("C65").Value = "'=IF(A65>=""9:00:00"",TRUE,FALSE)9時で等しいがTrueにならない"
    Range("B66").Formula = "=IF(A66>=""9:00""*1,TRUE,FALSE)": Range("C66").Value = "'=IF(A66>=""9:00:00""*1,TRUE,FALSE)*1で式の中で時間シリアル値になる"
    Range("B67").Formula = "=IF(A67>=""00:00:01""*1,TRUE,FALSE)": Range("C67").Value = "'=IF(A67>=""00:00:01""*1,TRUE,FALSE)*1で式の中で時間シリアル値になる"
End Sub