【Excel VBA】24時間を超える時刻の合計をセルの書式設定ではなくVBAのみで求める方法

前編では、Excelにおける時間の扱いや、24時間以上の時間を表示するためのExcel関数・セルの書式設定について紹介しました。後編では、Excel VBAを用いて24時間以上の時間の合計を求める方法を紹介します。なお、Google Apps Script(GAS)の24時間以上の計算については以下記事にて紹介しています。

【GAS】スプレッドシートで24時間以上の足し算【時間計算】

Googleスプレッドシート・GASで24時間以上の時間を計算する方法を紹介します。勤務表の合計時間を求める実例を図解を交えて分かりやすく解説していますので、初心者の方も必見です。

Excelにおける時間の扱いについて簡単におさらい
VBAにおける時間の計算
1. WorksheetFunction（ワークシート関数）を使用する
2. 時・分をバラバラにして計算する

Excelにおける時間の扱いについて簡単におさらい

はじめに少しだけ、Excelでの時間の扱い方についておさらいします。もう理解した！はよ解決策！という方は次項へ。

Excelの時間データとシリアル値

前編でも触れたとおり、Excelでは日付データを1900年1月1日を「１」とした連番で管理しています。表示のされ方としては、

・0:00:00～23:59:59は「h:mm」または「h:mm:ss」を表示

・24:00:00以降は「1900/1/1 0:00:00」を起点に経過した時間を表示

となります。23:59:59までの時刻は、デフォルトでは24:00:00以降のように日付部分が表示されません。

例えばセルに「23:00」と入力すると、「23:00:00」という日付データとして保持されます。

次に、セルに「24:00」と入力すると、「1900/1/1 0:00:00」という日付データとして格納されます。

24時間以上の時間は日付型が1900/1/1 0:00:00起点となり、想定したとおりに表示されなかったり、勝手に値が変わってしまう、といった意図しない現象に困ることがあります。前編ではセルの設定を利用して対応する方法を紹介しました。

これをふまえて、Excel VBAで時間の合計を求める方法を見ていきたいと思います。

おまけ

「1989年12月31日 23:00」と入力したらどうなるのか？

日付型ではなく、文字列として格納されました。「23:00」も「24:00」も値にプラス１をすると１日後の日付となりましたが、1899/12/31 23:00は#VALUE!エラーが発生します。ちなみにGoogleスプレッドシートにおけるシリアル値は「1899/12/30 0:00:00」からの経過日数となります。シリアル値なんてExcelで時間計算を扱うまで全然知りませんでした・・・・。24時間以上を入力したらシリアル値の表示に変わるなんて・・！嗚呼、ややこしや。

セルから時間を取り出すときの型について

A1セルの値を取得する際には、

Range("A1").Value　　　'セル番地で指定

もしくは

Cells(1, 1).Value　　　 'セルの座標で指定

で行います。

この際、戻り値はRangeオブジェクトとなります。

型に「Variant/Object/Range 」とありますが、型指定を明示的に行わない場合Variant型となります。ここではセルを取り出したため、Rangeオブジェクトとして戻り値が取得されました。

Textにはセルに表示されている内容そのものが、Value2には日付データの場合にシリアル値が格納されます。

VBAにおける時間の計算

では、Excel VBAで時間を計算する方法を見ていきたいと思います。

WorksheetFunction（ワークシート関数）を使用する

VBAでは、ワークシート内で使うことのできる関数が使えます。

WorksheetFunction.Text メソッドは、数値を書式設定した文字列に変換する関数ですので、数値（＝時刻データのシリアル値）を日付型表記に変換することができます。

WorksheetFunction.Text(数値, 表示形式)

表示形式	どのように表示されるか
h	「時」を先頭に0がつかない形で表示（8時間→8:00）
hh	「時」を1桁の場合は0つきで表示（8時間→08:00）
[h]	「時」を24時間を超える場合の数値でそのまま表示(25時間→25:00)
m	「分」を先頭に0がつかない形で表示（5分→0:5）
mm	「分」を1桁の場合は0つきで表示（5分→0:05）
[m]	「分」を60分を超える場合の数値でそのまま表示（95分→95）

WorksheetFunction.Text (1.075, "[hh]:mm:ss")

こちらの結果は

"25:48:00"

となります。

String型で戻り値が返りますが、セルに書き込みをする際にフォーマットが変わってしまい正しく表示されないことがあります。出力先のセルが決まっている場合には予めセルの書式設定＞ユーザー定義＞[hh]:mm:ss を選んでおくか、書き込み処理の前に以下のように書式を指定しておきます。

Range("A1").NumberFormatLocal = "[h]:mm"

書き込むセルが毎回異なる場合や、別のワークブックに書き込みたい場合などは後者の方法で対応できるかと思います。

時・分をバラバラにして計算する

ワークシート関数で”[hh]:mm:ss”形式にしろっていうのは、ちょっと拍子抜けというか、ナンセンスですね。

ワークシート関数をVBAで使用するのは簡単ですし、プログラミングの経験が浅い方でもExcelの操作に慣れていれば直感的に扱いやすいというメリットがあります。一方で、ワークシート関数を使用するとExcelが重くなる可能性があるというデメリットもあります。

これはなぜかというと、処理するたびに現在開かれているExcelの全てのワークシート関数が再計算されているためです。ワークシート関数自体が悪さをしているというよりも、ワークブックに入っているデータが膨大であったり、sumifやvlookupなど計算に時間がかかる関数を使っていたりすると処理が遅くて使い物にならないということもあるかもしれません。

そこで、独自の関数を作成して、セル依存にならない、他プラットフォームでも活用できるような関数を作ってみたいと思います。

A1～C1に入っている時間を合計する場合について以下にVBAプログラムを記載します。

こちらを実行すると、合計時間は19:50+10:35+9:50=40時間15分になるはずです。

↓コピー用（無断利用OK）

Public Function CalcTime()

    Dim hhSum As Integer
    Dim mmSum As Integer
    Dim commaIndex
    Dim StrTime As String

    For j = 1 To 3
        ' セルの値をString型で取得
        StrTime = timeVal + Cells(1, j).Text

        ' 「:」の位置を取得
        commaIndex = InStr(StrTime, ":")

        ' 時間のみ抽出
        hh = Mid(StrTime, 1, commaIndex - 1)
        ' 分のみ抽出
        mm = Mid(StrTime, commaIndex + 1, 2)

        ' 時間・分をInt型に変換
        If IsNumeric(hh) And IsNumeric(mm) Then
            hh = CInt(hh)
            mm = CInt(mm)
        End If

        ' 時間の合計
        hhSum = hhSum + hh
        ' 分の合計
        mmSum = mmSum + mm

        ' 分の合計が60以上の場合
        If mmSum >= 60 Then
            i = mmSum / 60
            '小数部のみ取り出し
            dcmMM = i - Int(i)
            resultMM = dcmMM * 60

            ' 時間の合計に分の整数部を足す
            resultHH = hhSum + Int(i)
            ' 時間・分の合計値を文字列で結合する
            result = resultHH & ":" & resultMM
        Else
            result = hhSum & ":" & mmSum
        End If
    Next j

    MsgBox result

End Function

Functionとして関数を作成すると、引数を渡すことも可能です。セルから取得した値をコレクションに格納して、時間の合計以外にも使用する場合にはコレクションを引数として渡して合計時間を戻り値としても良いですね。

13行目では、Instr関数を使用して「:」がある位置を取得しています。

' 「:」の位置を取得
commaIndex = InStr(StrTime, ":")

        ' 時間のみ抽出
        hh = Mid(StrTime, 1, commaIndex - 1)
        ' 分のみ抽出
        mm = Mid(StrTime, commaIndex + 1, 2)

次に、Mid関数を使用して時間と分をそれぞれ取り出します。

Mid (string, start, [length])

時間の取り出しは開始位置を1として「:」の前まで、

分の取り出しは開始位置を「:」の位置＋1として2文字分抽出します。

        ' 時間・分をInt型に変換
        If IsNumeric(hh) And IsNumeric(mm) Then
            hh = CInt(hh)
            mm = CInt(mm)
        End If

そして、以降で時間の計算を行うために、取り出した時間と分をそれぞれIntに変換しておきます。Cint関数で文字列を数値に変換する際に、変な文字列が入っていてエラーが起こることを回避するために数値にできるかどうかをチェックしておきます。（この関数に渡す前にちゃんとした形にしておけばエラーになることはないですし、仮にエラーが起こりうるにしてもエラー処理を入れておけばいいのですが・・・。今回は、変換できない場合にそのまま処理が進んでもエラーで落ちることはないので、上記のように記載しています。）

続いて、時間と分をそれぞれ合計します。

        ' 時間の合計
        hhSum = hhSum + hh
        ' 分の合計
        mmSum = mmSum + mm

上記のように分と時間を別々で計算すると、分の値が60以上になる場合があります。今回の例でも、分の合計は60を超えますね。

そこで、分の合計が60以上の場合には以下のように処理します。

まず、分の値を60で割り、

そこから小数部分のみ取り出します。

小数になっている分を時刻型に変換します。変換するには60を掛けます。

最後に、時間の合計に分の整数部分を足し、文字列結合させます。

分の値が60未満の場合はそのまま文字列結合させればOKです。

上記のとおり、24時間以上の値が正しく表示されました。

chaso

文系出身、数字が苦手な平凡主婦。塾講師、大手企業SE、不動産事務、Webライター、結婚後はパートタイムでエンジニアをしています。機械音痴だけど効率化や自動化をこよなく愛しています！お仕事の依頼・ご相談は問い合わせよりお願いいたします♪

【Excel VBA】24時間以上の合計を求める方法（後編）