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 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 simply 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 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 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 Border and Interior 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. One way can be using Now() with the format function.

In the format function, specify the date format that you want to get a date. For example, “dd/mm/yyyy”.

See an example below where we will populate the same sheet above by date only:

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: