このサイトはCocoonを使っています。現在「ミックスブルー [作者: y.hiroaki氏]」スキンを適用中です(^^)/

Excel VBA Date など日付・時刻関数の使用例について

スポンサーリンク

Excelで日付や時刻を扱うときに、書式設定などで結構苦労した経験があるのではないでしょうか。日付関連をVBAマクロで処理することも多いと思いますが、これから紹介する日付・時刻関連のマクロ処理を理解すれば日付関連で悩むことが無くなるでしょう

くるみこ
くるみこ

お待たせしました! 今回は Date 関数など、日付や時刻の扱いについて知っておいたほうが良いと思うことを解説します。

すみません! InputBox の復習はできましたが、日付・時刻の予習はできませんでした。日付って思ったように表示できないことが多いので是非詳しく教えてください!よろしくお願いしますm(_ _)m

前回のおさらいはこちらの記事です(^^)/ 下のカードをクリックすれば開きます

くるみこ
くるみこ

前回記事でわかったことは
・InputBox関数 と InputBoxメソッド の違いと使い方がわかりました
InputBox関数 の戻り値は文字列なので数値を扱う場合変換が必要
・InputBoxメソッド は Type で各種の戻り値を指定できます

【この記事でわかること
・日付に関連する関数の種類がわかります

日付取得時の注意点がわかります
日付・時刻関連のVBAマクロの使用例がわかります

スポンサーリンク

日付・時刻関数処理内容一覧と使用例

ExcelVBAには日付・時刻に関する関数が多数あります。主な関数を一覧表にしてみました

 関数処理内容
1Date現在のシステム日付を含む Variant (Date) を返す
2DateAdd指定した時間間隔を日付に加算してその日付を含む Variant (Date) を返す
3DateDiff2つの指定した日付の時間間隔を指定する Variant (Long) を返す
4DatePart日付の指定した部分を含む Variant (Integer) を返す
※ この関数の使用には問題があります
5DateSerial指定した年、月、日の Variant (Date) を返す
6DateValue日付の Variant (Date) を返す
7Day月の特定の1日を表す1から31の範囲の整数 Variant (Integer) を返す
8Hour1日の時間を表す0以上23以下の整数 Variant (Integer) を返す
9Minute分を表す0から59までの整数を指定する Variant (Integer) を返す
10Month月を表す1から12までの整数を指定する Variant (Integer) を返す
11MonthName指定した月を示す文字列を戻す
12Nowコンピューターのシステムの日付と時刻に基づいて
現在の日付と時刻を指定するVariant (Date) を返す
13Second秒を表す0から59までの整数を指定する Variant (Integer) を返す
14Time 現在のシステム時間を示す**バリアント型 (Variant) **(日付) を返す
15Timer午前0時からの経過秒数を表す単精度浮動小数点数型 (Single) の値を返す
16TimeSerial特定の時、分、および秒の時刻を含む Variant (Date) を返す
17TimeValue時刻が含まれる バリアント (Variant) 型 (日付 (Date) 型) の値を返す
18Weekday週の曜日を表す整数が含まれる Variant (Integer) を返す
(1:日曜日~7:土曜日)
19WeekdayName指定した曜日を示す文字列を返す
20Year年を表す整数を含む Variant (整数) を返す
参照元:https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/

日付・時刻関数マクロ使用例

20個の日付・時刻関係の関数のマクロサンプルです

'日付関連関数のテスト
Sub DateFunction_Sample()
    Cells(1, 1) = Date                          'yyyy/mm/dd
    Cells(2, 1) = DateAdd("m", 3, "2020/11/30") '2021/1/31
    Cells(3, 1) = DateDiff("d", "2020/11/30", "2021/1/31") '62
    Cells(4, 1) = DatePart("m", Date)           'mm
    Cells(5, 1) = DateSerial(2021, 1, 12)       '2021/1/12
    Cells(6, 1) = DateValue("2021, 1, 12")      '2021/1/12
    Cells(7, 1) = Day(Date)                     'dd
    Cells(8, 1) = Hour(Now)                     'hh
    Cells(9, 1) = Minute(Now)                   'dd
    Cells(10, 1) = Month(Date)                  'mm
    Cells(11, 1) = MonthName(Month(Date))       'mm月
    Cells(12, 1) = Now                          'yyyy/mm/dd hh:mm
    Cells(13, 1) = Second(Now)                  'ss
    Cells(14, 1) = Time                         'hh:mm:ss
    Cells(15, 1) = Timer                        's.m
    Cells(16, 1) = TimeSerial(8, 30, 0)         '8:30:00
    Cells(17, 1) = TimeValue("12:0:10")         '12:00:10
    Cells(18, 1) = Weekday(Now)                 'w(曜日番号)
    Cells(19, 1) = WeekdayName(Weekday(Now))    'w曜日
    Cells(20, 1) = Year(Now)                    'yyyy
End Sub

日付関連書式設定にはFormat関数が必須

・日付関係の書式設定をする場合、Format 関数を使用することが必須だと思います
・設定する場合の記号を知っておく必要がありますので一覧表を作っておきます

日付関連書式記号一覧表

種類記号表示例
西暦年yy21
西暦年yyyy2021
和暦元号(記号)gR
和暦元号(略称)gg
和暦元号ggg令和
e3
年(2桁表示)ee03
m1
月(2桁表示)mm01
mmm1月
種類記号表示例
mmmmJanuary
mmmmmJanuary1
d8
日(2桁)dd08
曜日dddFri
曜日ddddFriday
曜日aaa
曜日aaaa金曜日
(曜日)(aaa)(金)

「書式指定文字」使用のマクロサンプル

Date 関数データを Format 関数で成型するマクロ例です

'Date関数で取得したデータをFormat関数で成型する
Sub DateFormat_Sample()
    Cells(22, 1) = Format(Date, "yyyy/mm/dd")   'yyyy/mm/dd
    Cells(23, 1) = Format(Date, "yyyy年m月d日")  'yyyy年m月d日
    Cells(23, 1) = Format(Date, "yyyy年mm月dd日")  'yyyy年mm月dd日
    Cells(24, 1) = Format(Date, "ggge/m/d")     '令和e/m/d
    Cells(24, 1) = Format(Date, "ggge/mm/dd")   '令和e/mm/dd
    Cells(25, 1) = Format(Date, "yyyy/mmm/ddd(dddd)") 'yyyy/mmm/ddd(dddd)
    Cells(26, 1) = Format(Date, "ggge/mm/dd(aaaa)")   '令和e/mm/dd(aaaa)
End Sub

Format 関数

・書式指定式の指示に従って書式設定される式を含む Variant (String) を戻します

構文
Format(Expression, [Format], [FirstDayOfWeek], [FirstWeekOfYear])

Expression だけ必須(その他は省略可能)です
「年月日」の場合の例は、Format(Expression, “yyyy/m/d”) のように指定します

・日付記号

記号範囲
d1〜31(前に0を付けない、月の日付)
dd01〜31(前に0を付ける、月の日付)
w1〜7(週の曜日。土曜日=1から開始)
ww1〜53(1年のうちで何週目かを表す数
値前に0を付けず、1月1日から開始)
m1〜12(月。前に0を付けず、1月=1
から開始)
mm01〜12(月。前に0を付け、1月=01
から開始)
mmm月の省略名を表示(イスラム暦の月
の名前には省略形はありません)
mmmm完全な月名を表示
y1〜366(1年のうちで何日目かを表す
数値)
yy00〜99(西暦年の下2桁)
yyyy100〜9999(3桁または4桁の西暦年)

・時間記号

記号範囲
h0〜23(「AM」または「PM」を追加した
1〜12の数値) (前に0を付けない、1日の
中の時間)
hh00〜23(「AM」または「PM」を追加した
01〜12の数値) (前に0を付ける、1日の
中の時間)
n0〜59(前に0を付けない、1時間の中の分)
nn00〜59(前に0を付ける、1時間の中の分)
m0〜59(前に0を付けない、1時間の中の分)
hまたはhhが先行する場合のみ
mm00〜59(前に0を付ける、1時間の中の分)
hまたはhhが先行する場合のみ
s0〜59(前に0を付けない、1分の中の秒)
ss00〜59(前に0を付ける、1分の中の秒)

日付や時刻はシリアル値で管理

Excelは日付や時刻は、「シリアル値」で管理しています。シリアル値は「1900/1/1」を1として1日に1加算され、整数部は日付で小数点以下は1日内の時刻を表します

時刻を指定した時間の計算例

シリアル値の1日以内の小数部は、午前0時から秒単位で加算されます。1日のうち半分に当たる午後12時は「0.5」です。午前9:30:00は「0.395833333333333」となります
シリアル値はセルに記入したりメッセージボックスに表示すると変数のデータ型を日付に設定してあれば自動的に日付で表示されます。シリアル値を見たい場合は、セルの場合は数値に設定して小数点以下を表示します。変数の場合は倍精度浮動小数点数型に変換すれば見ることが出来ます

'終了時刻と開始時刻の差で経過時間を計算する
Sub 時間計算例_1()
    Dim t1 As Date, t2 As Date
    t1 = TimeValue("9:30:00")
    t2 = TimeValue("15:15:00")
    Cells(1, 15).NumberFormatLocal = "h:mm"     'セル書式を日付に設定
    Cells(1, 15) = t2 - t1                      '結果例:5:45
End Sub

勤務時間計算(シリアル値使用)例

前述の「時間計算例_1」の時間計算は「t2 – t1」の部分です。日付型の変数ですが、実際には中身のシリアル値で計算しています

シリアル値 = 1 = 1日です。1分 = 1日/24時間/60分 → 0.000694444 となります

次の例では、昼休みの1時間をシリアル値で定数設定して使用している例です
設定条件は、出社時刻はフレックスのパート勤務で、終了時刻は16:00で全員終了となる設定です。また昼休みは12:00~13:00としています。出社時刻から勤務時間を出すサンプルです

'シリアル値を使った勤務時間計算例
Sub 時間計算例_2()
    Const jikan As Single = 0.041667    '昼休み時間をシリアル値で定数化
    Dim lunch As Single
    Dim t1 As Date, t2 As Date
    Dim msg As String
    t2 = TimeValue("16:00:00")  'TimeValue関数で勤務終了時刻をt2に代入
    msg = "出勤時間を入力して下さい" & Chr(10) _
        & "(例:9:15 又は、13:00 or PM1:00)"
    t1 = InputBox(msg, "出勤時間")      '出勤時刻を入力要求
    If t1 > 0.666666666666667 Then      '入力値が16:00以降の場合抜ける
        MsgBox msg
        Exit Sub
    End If
    '昼休み時間を判定
    If t1 < 0.5 Then    '12:00のシリアル値は0.5
        lunch = jikan   '12:00以前に出勤していればIunchに定数代入
    Else                '12:00~1:00の間に出勤した場合は13:00出社とする
        If (0.5 + jikan) > t1 Then t1 = TimeValue("13:00:00")
    End If
    MsgBox Format(t2 - t1 - lunch, "h:mm") '勤務時間の計算結果を表示
End Sub

シリアル値を計算して年齢算出例

生年月日を指定すると現在の年齢を算出するマクロ例です
なお、前の例では「TimeValue関数」で1日以内の1以下の小数点計算でしたがこのシリアル値は「DateValue関数」で戻し整数部の日付を変数に代入しています

'シリアル値を計算した年齢算出例
Sub 時間算出例_3()
Dim msg As String, ymd As Date, Inymd As String
    msg = "生年月日を入力して下さい" & Chr(10) _
            & "yyyy/mm/dd のように/で区切って入力して下さい"
    Inymd = InputBox(msg, "生年月日入力")
    '日付データが入力されたかどうか判定する
    If IsDate(Inymd) = False Then
        MsgBox "入力データは日付に変換できません"
        Exit Sub
    Else
        ymd = DateValue(Inymd) '日付データを件数に代入
        End If
    Call 時間算出例_4(ymd)  '年齢算出プロシージャ呼び出し
End Sub

上記14行目で、次の年齢計算用のプロシージャを呼び出しています

'年齢計算(うるう年を考慮して年齢算出する例)
Sub 時間算出例_4(DateOfBirth As Date)
    Dim age As Long
    'Int関数でデータを整数にまるめます
    age = Int((Date - DateOfBirth + 1) / 365.25) 'うるう年を考慮して算出
    MsgBox DateOfBirth & "生まれの方の" & Chr(10) & _
    "今現在の年齢は「" & age & "」歳です。"
End Sub

5行目の計算式で、1年の日数「365.25」としているのは、4年に一回うるう年があるので、1年365日にプラス0.25追加しています(4年に1回366日)
ただし、厳密にいえば、100年に1回はうるう年に当ってもうるう年になりません。また、この100年周期のうち400年に1回はうるう年になるのです。単純な「0.25」追加は100年に1回は1日の計算が狂うことになりますが、年齢値の計算には大きな問題はないでしょう

スポンサーリンク

まとめ(おわりに)

くるみこ
くるみこ

Date関数など、日付・時刻関連の関数を使いこなすための解説はひとまずおしまいです(^^)/ ここまでは理解できましたか?

はい! シリアル値のことが勉強できたので日付・時刻についての疑問はなんとなくわかりました。復習でいろいろ試してみます!

くるみこ
くるみこ

では、次回は 日付時刻関連のマクロの使い方についてもう少し解説しておきましょう! 復習はしっかりやっておいてくださいね!

まとめと感想など

・日付に関連する関数の種類がたくさんあることがわかりました
日付関連書式設定にはFormat関数を使うことがわかりました
・シリアル値がどういうものか計算方法がわかりました

日付・時刻関連のVBAマクロの使用例がわかりました

マクロ(VBA)を実行する際は必ずバックアップを取ってから行ってください
・マクロ(VBA)は実行後にファイルを保存すると元に戻すことはできません!
・実行後にファイルを保存せず終了すれば、実行前に戻すことができます!


ブログランキングに参加しています(^^)応援よろしくお願いしますm(_ _)m

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
当面は「初心者向けマクロVBA」の記事を継続して書いていきます

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう現在検討中です
・その他雑記的に「小ネタなどいろいろ」・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください

スポンサーリンク

スポンサーリンク