Excel/VBA Now() function: Get Current Date and Time

What is Now() function in VBA?

• The Now() function returns the current date and time from the computer system.
• It returns variant (Date).

We will show you how to display the date and time in the message box, assign to a date variable, and use Now() to fill in the Excel sheet range of cells – in the examples below.

Display the current date and time in the message box

The example below displays the current system date and time in a message box by Now() function:

Sub now_ex()
    MsgBox Now()
End Sub

Result:

Assigning to a date variable and display

We declared a variable of date type and assigned it the current date and time by the Now() function.

Then we displayed the variable value in the message box:

Sub now_ex()

Dim dt_curr As Date

dt_curr = Now()

    MsgBox "Current Date and Time = " & Now()

End Sub

Result:

Using a Range of Cells and writing the current date and time

For this example, we have the following sheet:

We created a range of cells (E2:E11).

Then, I used a For..Each loop.

In each iteration, we write the date and time in the range of cells (one by one).

Code:

Sub now_ex()

Dim rng_curr As Range

Set rng_curr = Range("E2:E10")

For Each cell In rng_curr
  
    cell.Value = Now()
Next

End Sub

Result:

Setting the Borders and Interior color of above to match the rest

Just to match the color scheme of the date/time column with the rest in the above sheet, we added a Border and Interior/background color with Now() function:

Sub now_ex()

Dim rng_curr As Range

Set rng_curr = Range("E2:E10")

For Each cell In rng_curr

    cell.Value = Now()
    cell.Interior.Color = RGB(255, 255, 204)
    cell.Borders.Color = RGB(178, 178, 178)

Next

End Sub

Result:

Extract only date by using Now() function example

Don’t want to get time as using the Now() function?

Sub now_ex()

Dim rng_date_now As Range

Set rng_date_now = Range("E2:E10")

For Each cell In rng_date_now


    'Getting only date from Now() function

    cell.Value = Format(Now(), "dd/mm/yyyy")
    cell.Interior.Color = RGB(255, 255, 204)
    cell.Borders.Color = RGB(178, 178, 178)

Next

End Sub

Result:

Get time only using Now() function

Similarly, you may only get the time by using “hh:mm:ss” in the format function with Now().

We display the current time in the message box:

Sub now_ex()

MsgBox "Current Time is: " & Format(Now(), "hh:mm:ss")

End Sub

As we executed the above code, the output: