How to insert current time in Excel: timestamp shortcut, formula, VBA

How to insert time in Excel using shortcut

To insert the current time into an Excel cell as a static timestamp, use one of the following keyboard shortcuts:

• To insert current time, press Ctrl + Shift + ;
• To enter current date and time, press Ctrl + ; which inserts a date, then press the Space key, and then hit Ctrl + Shift + ; to insert the current time.

The result is a static value that won't change when a workbook is recalculated.

Insert current time as static value with VBA

Another way to insert the current date and time in Excel as a static value without auto-updating is by using VBA. Here's how:

1. Insert the following code in the Visual Basic Editor. The detailed instructions are here: How to add VBA code in Excel.

   Sub NowFunction() Application.ActiveCell.Value = Now() End Sub

2. Create a button from a shape or any other graphic object and link the macro to the button as explained in this example: How to make a macro button in Excel.
3. Select the cell where you want to insert the date and/or time as a static value.
4. Click the macro button.
5. Apply the desired time format to the cell. Done!

On the below image, the cell is formatted to display only time, but the formula bar shows the entire date time value, which was returned by the macro and is now stored in the cell.

Insert current date and time using NOW function

If you aim to insert the current date and time as a dynamic value that refreshes automatically, then use the Excel NOW function.

The formula is as simple as it can possibly be, no arguments are required:

=NOW()

Like the timestamp shortcuts, the NOW function inserts the current date and time in the default format defined in your Region settings. You can easily change this by applying a custom date and time format.

Here are a few examples of how the Excel NOW function results can be formatted:

When using the NOW function in Excel, there are a few things to keep in mind:

• The time is retrieved from your computer's system clock.
• NOW is a volatile function that recalculates every time the worksheet is re-opened or recalculated.
• To force a NOW formula to update, press either Shift + F9 to recalculate the active worksheet or F9 to recalculate all open workbooks.

For more details, please see How to use NOW function in Excel.

Insert current time as dynamic value

If you wish to insert only the current time without a date, you have the following choices:

1. Format NOW formula to only show time.

   Use a regular NOW formula, and then apply the time format you want to the result:

   =NOW()

   Please remember, this will only change the display representation. The actual value stored in a cell will still be a decimal number consisting of an integer representing the date and a fractional part representing the time.

2. Return only the time value.

   Make use of the INT function to round the decimal number returned by NOW() down to the nearest integer. And then, subtract the integer part representing today's date so that only the fractional part representing the current time is output.

   =NOW() - INT(NOW())

   Another option is to subtract the integer part representing the date (returned by TODAY) from the date time value (returned by NOW):

   =NOW() - TODAY()

   By default, both formulas return a decimal number representing the time internally in Excel. To get a correct result, apply the Time format to the cell.

The following screenshot demonstrates both formulas in action. Pay attention that although the formatted time values look the same (column D), the actual values stored in cells (column B) are different - the results returned by the second formula (B16:B20) contain only the fractional part.

Insert current hour in Excel

To insert the current hour as a number between 0 (12:00 AM) and 23 (11:00 PM), use the HOUR and NOW functions together:

=HOUR(NOW())

Yep, it's that simple :)

How to convert Excel timestamp to date

Assume you have a column of timestamps in your worksheet from which you wish to remove the time value and keep only the date. This can be done in a few different ways.

How to get hours, minutes and seconds from timestamp

To extract a specific time unit from a time stamp, you can use the following functions:

HOUR(serial_number) - returns an hour of a time value, as an integer from 0 (12:00 am) to 23 (11:00 pm).

MINUTE(serial_number) - gets the minutes of a time value, as an integer from 0 to 59.

SECOND(serial_number) - returns the seconds of a time value, as an integer from 0 to 59.

In all three functions, serial_number can be supplied as a reference to the cell containing the time, text string enclosed in double quotes (for example, "6:00 AM"), decimal number (e.g. 0.25 representing 6:00 AM), or the result of another function.

In the image below, the following formulas are used.

To return hours from the timestamp in A2:

=HOUR(A2)

To get minutes from the timestamp in A2:

=MINUTE(A2)

To extract seconds from the timestamp in A2:

=SECOND(A2)

How to insert specific time in Excel

When you type a time value in a cell, in most cases Microsoft Excel recognizes it as such and automatically applies the Time format. If Excel does not understand what you are entering, the TIME function can help. The syntax is intuitive and straightforward:

The hour, minute and second arguments can be provided as numbers from 0 to 32767. The supplied numbers are processed in this way:

• If hour is greater than 23, it is divided by 24 and the remainder is taken as the hour value.
• If minute is greater than 59, it is converted to hours and minutes.
• If second is greater than 59, it is converted to hours, minutes, and seconds.

For example:

• TIME(30, 0, 0) equates to TIME(6, 0, 0), which is 6:00 AM.
• TIME(0, 130, 0) is converted to TIME(2, 10, 0), which is 2:10:00 AM.
• TIME(0, 0, 130) is converted to TIME(0, 2, 10), which is 12:02:10 AM.

The Excel TIME function is particularly useful when it comes to combining individual units into a single time value.

Assuming you have hours in A3, minutes in B3 and seconds in C3, the following formula will put them together:

=TIME(A3, B3, C3)

Practice workbook for download

Insert time in Excel - examples (.xlsx file)