vbaの日付や時間操作はとても難しいことが多い。
その中で、24時間以上の時間をセルに表示することに苦労したので、最終的にうまくいったコードと解説をしていきます。
これをすることで、下記のような計算したいときに便利
・残業時間計算で、日付またぎの時刻の計算が可能
※ 本来は日付またいで仕事しないように・・・
では、見ていきましょう！
24時間以上の作り方_VBAコードの解説
今回の定義
- 現在時刻から24時間以上経過した場合の表記をする
- 固定で1日超えた場合にしています。変数にすることで2日以上も可能
- 1時20分33秒の場合 ⇒ 25時20分33秒と表記
出来上がりVBAコード
まずは出来上がりのコードを見てみましょう。このまま利用していってもらっても大丈夫です。
今回の例は、現在時刻から1日超えた場合の指定しています。
変数のoverDayを、固定値ではなく日数を自動計算して取得できるように作れば、
何日後の表記(例: 3日後の50:10:08表記など)もできるので、ここは使う用途によって変更してみましょう。
24時間以上の時刻を取得 例：25:10:45
' 変数宣言
Dim nowTime As Date
Dim hourTime As Integer
Dim overDay As Integer
Dim overTimeTwentyFourHour As String
' 現在時刻の取得
nowTime = Now
' 24時間以上が何日分かの日数を取得
overDay = 1
' 何時間(h)かを計算。何日分(日数×２４時間)を超えているかをプラスしている
hourTime = (overDay * 24) + Hour(nowTime)
' 24時間表記Hour : nn(分表記(時刻をフォーマット指定しない場合):ss(秒))
overTimeTwentyFourHour = hourTime & Format(nowTime, ":nn:ss")
' セルA1に24時間以上の時刻表記
Cell(1, 1).Value = overTimeTwentyFourHour それぞれ、解説していこうかと思います。
現在時刻の取得
' 現在時刻の取得
nowTime = Nowこちらの”Now”を指定すると、現在時刻のyyyy/mm/dd hh:mm:ssのDate型で取得することが可能です。
何日分かの取得
' 24時間以上が何日分かの日数を取得
overDay = 124時間以上の計算として、日数×24時間を時間(h)に足していきます。
そのための、日数を宣言しておきます。
今回は固定値で1日としました。
ここを計算式で自動計算する仕様に変更することで、数日後の表記もとれるように設定しておきましょう。
24時間以上の時間(h)を取得
' 何時間(h)かを計算。何日分(日数×２４時間)を超えているかをプラスしている
hourTime = (overDay * 24) + Hour(nowTime)24時間超えた(h)の数値を取得しておきます。
例えば、25時間なら”25”、31時間なら”31″と数値型で取得しておきましょう。
24時間以上の文字列を取得
' 24時間表記Hour : nn(分表記(時刻をフォーマット指定しない場合):ss(秒))
overTimeTwentyFourHour = hourTime & Format(nowTime, ":nn:ss")Format関数では、Date型を指定の表示方法でString型にすることが可能。
これを用いて、hourTime(24時間以上の(h)の数値) ＋ “:nn:ss”の分・秒表記を後ろにくっつけるで24時間以上の表記が可能となります。
※変数名がだいぶ長くなりました(汗)・・・ご自身でわかりやすいような短めにしてもらってよいかと。
もし、DateAdd関数を利用してnowTimeを25:10:00などにしておいても、
Format関数の通常の利用方法であるFormat(nowTime, “hh:nn:ss”)では、hhの表記が24時間表記に代わり、
1:10:00となってしまう。これを防止するために、hhは数値型で別途で保持しておくことが必要であった。
◆Format型の補足
| Format(Date型, “表記方法”) | Date型の値を表記方法の通りにする。 String型で返す。 ◆表記方法の例 ・ “yyyy/mm/dd” ⇒ 2023/10/09 ※日付の表記(区切りは”/”) ・ “yyyy-mm-dd” ⇒ 2023-10-09 ※日付の表記(区切りは”-“) ・ “hh:nn:ss” ⇒ 10:33:01 ※時刻の表記(区切りは”:”) (Month(月)で”mm”となり、Minute(分)は”nn”となる) |
セルへ出力
' セルA1に24時間以上の時刻表記
Cell(1, 1).Value = overTimeTwentyFourHour 今回はA1セルに出力しました。
結果：現在時刻が1:20:33 ⇒ 25:20:33と表記されます。
◆補足
出力セルの中身を見ると”1900/1/1 1:20:33″のように、“1900/1/1″が埋め込まれています。
これはExcelの仕様らしく、25時間以上は、1900/1/1と24時間以内表記になるらしいです。
セル書式設定の表示形式を変更しておく
セルの書式設定⇒表示形式⇒ユーザー定義にて、
“h:mm:ss”と指定してしまうと、24時間以内表記になってしまいます。
24時間以上を表示する場合には、
ユーザー定義にて”[h]:mm:ss”とhを[]で囲んでおくことで表記ができますので設定しておきましょう
ユーザー定義： “[h]:mm:ss”と指定する
24時間以上のセル引き算などに利用
下記のように、例えば終了時間の欄を今回説明した24時間以上の現在時刻を入れることをうまく活用すれば、
終了時間が日付をまたいでも、時間差分をうまく表記することが可能になります。
まとめ
今回はFormat関数の時間(h)部分を数値で計算しておいて、後ろにFormat関数で”:nn:ss”をくっつけることでうまく24時間以上表記が可能になりました。
VBAは日付や時刻の方法がなかなか難しく、参考になっていれば幸いです。
以上になります。読んで頂いた方、ありがとうございました。