Excel VBA Time Function

TIME is a kind of volatile function. It does not have any syntax to it.

We also have a similar function in Excel – NOW () functionNOW () FunctionIn an excel worksheet, the NOW function is used to display the current system date and time. The syntax for using this function is quite simple =NOW ().read more, which inserts both current times and the current date in the spreadsheet.

TIME ()

We need to enter the function. No need for parenthesis to enclose. Just the TIME function is enough to insert the current time. The result given by the TIME function is in the String.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Time Function (wallstreetmojo.com)

How to Use TIME Function in VBA?

Let me show you an example of a simple TIME in excelTIME In ExcelTime is a time worksheet function in Excel that is used to calculate time based on the inputs provided by the user. The arguments can take the following formats: hours, minutes, and seconds.read more function. Then, follow the below steps to create code to use the TIME function.

Step 1: Create a Macro.

Code:

Sub Time_Example1() End Sub

Step 2: Declare a variable as String.

Sub Time_Example1()

Dim CurrentTime As String

End Sub

Step 3: Assign a value to this variable through the TIME function.

Sub Time_Example1()

Dim CurrentTime As String

CurrentTime = Time

End Sub

Step 4: Now, show the result in the message box.

Sub Time_Example1()

  Dim CurrentTime As String

CurrentTime = Time

MsgBox CurrentTime

End Sub

Run this code using the F5 key or manually. We will get the current time.

So, when we ran this code, the time was 11.51.54 AM.

Alternative of Now() Function

Combination of Date & Time as an Alternative to NOW Function

As we said at the beginning of the article, the NOW function can insert the current date and time. However, we can use two other functions as an alternative to the NOW function. Those two functions are VBA DATEVBA DATEVBA Date is a date and time function. It returns only the current date as per the system date you are using and has no arguments whatsoever. This function returns the current system date.read more & VBA TIME functions.

VBA Date will return the current date, and the Time function will return the current time, making the NOW function. Below is a set of codes that will insert the current date and time in cell A1.

Sub Time_Example2()

Range(“A1”).Value = Date & " " & Time

End Sub

This code will insert the current date and time in cell A1.

We can also apply a format to these values using the FORMAT function. For example, the below code will format the date and time.

Sub Time_Example2()

Range(“A1”).Value = Date & " " & Time

Range(“A1”).NumberFormat = “dd-mmm-yyyy hh:mm:ss AM/PM”

End Sub

Now, the result of this code is as follows.

Track Your Workbook Open Records using Time Function in VBA

Often, we need to know our workbook opening time frequency. For example, there is a situation where we open the workbook quite often, and we make some changes. Then, we can track the workbook opening time and date by tracking the workbook opening time.

Create a new sheet and rename it “Track Sheet.”

Step 1: Double-click on ThisWorkbook from VBE Editor.

Step 2: Select the workbook from the object drop-down list.

Step 3: As soon as you select this option, you can see a new Macro automatically created by itself in the name “Workbook_Open().”

Step 4: Inside this Macro, we will need to write a code to track the workbook opening date and time.

We have already written the code. Below is the code for you.

Private Sub Workbook_Open()

Dim LR As Long

LR = Sheets(“Track Sheet”).Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets(“Track Sheet”).Cells(LR, 1).Value = Date & " " & Time() Sheets(“Track Sheet”).Cells(LR, 1).NumberFormat = “dd-mmm-yyyy hh:mm:ss AM/PM”

End Sub

It will record your workbook opening times like the below one.

This article has been a guide to VBA Time Function. Here, we learned how to use the VBA Time function and the combination of date and time as an alternative to the NOW() function and track the open workbook records along with examples and a downloadable Excel sheet. Below are some useful Excel articles related to VBA: –

  • VBA Paste MethodTime Card TemplateExcel Convert FunctionCDATE Function in VBA