[Excel VBA] How to Branch Processing by Comparing Current Time (CDate & Time Function)

2025年12月30日

When building processes in VBA, you often need to create conditional branches based on time, such as “stop processing if the current time is past XX:00.”

I actually had a requirement to “prevent data writing after 5:00 PM,” so I researched and implemented a method to achieve this.

What I Want to Achieve

Compare the current time with a limit time (e.g., 17:00).
Restrict processing if the current time is past the limit, otherwise execute the process.

Key Point: Convert Strings to Time Type with CDate

In VBA, you can use the CDate function to convert strings or numbers into the Date/Time type (Date type). If you do not perform this conversion, the comparison will be treated as text, which may lead to incorrect results.

The Code Used

Below is the sample code I actually used.

Dim now_time, limit_time

now_time = CDate(Format(Time, "hh:mm:ss"))
limit_time = CDate("17:00:00")

If now_time > limit_time Then
    MsgBox "It is past 17:00. Please do not enter data."
Else
    MsgBox "It is before 17:00, so you can write data."
End If

Explanation of the Code

Dim now_time, limit_time Declares variables to hold the current time and the limit time.

now_time = CDate(Format(Time, "hh:mm:ss")) Gets the current time using the Time function, formats it using the Format function, and then converts it to the Date type using CDate.

limit_time = CDate("17:00:00") Converts the time literal directly into the Date type.

If now_time > limit_time Then Compares the times. If the current time is past 17:00, the result is True.

MsgBox Displays an appropriate message based on the condition.

Common Mistake: Comparing as Strings

I initially tried comparing strings like “17:00:00” directly, but I struggled because the comparison operators (> and <) did not work correctly.

To compare as time, conversion to the Date type (CDate) is always necessary. Also, do not forget to format the time correctly using Format(Time, "hh:mm:ss").

Tips for Application

If you use Now instead of the Time function, you can compare both the date and time.
Instead of a MsgBox, you can use this logic to stop writing to cells or interrupt a process.
You can use the same method for comparisons in minutes or seconds.

Summary

If you want to compare the current time with a specific limit time in VBA, using CDate to convert both to the Date type allows for correct comparison using inequality operators.

Using this method, you can easily implement controls such as “stop processing when the time comes” or “allow processing before a certain time.”