If you regularly input lots of time data in Excel; such as clock-in/ clock-out details, manually looking them up and entering them can be quite a tedious process.

Instead, you can use various keyboard shortcuts and built-in Excel functions to enter a time value much more quickly. On top of it, you can change time formatting and use a preferred type, such as one, excluding the date or including the seconds.

Similarly, you can choose a custom format to avoid having to type out the **colon** when entering a time value manually.

## Using Shortcut Keys

This is perhaps the quickest way to get the current time in Excel. Whether you want the full date and time or only the timestamp, you can use the Excel shortcuts mentioned below.

**Current Timestamp Without Date: **Ctrl + Shift + ;
**Sample output: **12:10 PM

or,

**Current Date with time: **Ctrl + ; and Ctrl + Shift + ;
**Sample output: **4/16/2023 12:15

**Note:**The above shortcuts return the time values based on regional settings of your system. So, if you get a wrong value, set the corrrect regional setting from your control panel.

## Using the NOW Function

The **NOW** function returns the current date/time value without requiring any argument.

**Syntax:=**NOW()

By default, Excel returns the current date and time such as **3/29/2023 02:10** with this function.

To get the **current time only**, you can use a custom number/date formatting. Additionally, you can choose other time formats to display seconds if you want.

- Select the cell (s) with time values.
- Press Ctrl + 1 to launch the Format Cells window.
- Then, click the Number tab and select
**Time**under the Category section. - On the right pane, choose a preferred time format such as
**1:30 PM**with only time. Check the sample output under the Sample section. - Alternatively, choose
**Custom**under Category and enter your preferred time format such as**h:mm:ss AM/PM**. - When done, click OK and close the window.

**Note:**The above function is a volatile function and re-evaluates its value whenever you perform any calculation or re-open the workbook. So, avoid using it if you have large datasets as it could make the worksheet and the Excel app load extremely slowly.

## How to Enter Time after a Specific Hour, Minute, or Second?

Instead of the current time, you can even **calculate the time** before/after the current time. For this, we can use the TIME function along with the NOW function.

**Syntax**:=TIME(hour,minute,seconds)

**To add time:**

=TIME(hour, minute, seconds) + NOW()**To subtract time:**

=TIME(hour, minute, seconds) – NOW()

For instance,`=NOW()+TIME(1,0,0)`

returns the time after one hour of the current time.

You can also get the time after certain minutes and seconds by specifying them in the TIME function.

Similarly, the formula `=NOW()-TIME(1,0,0)`

returns the time before one hour of the current time.

## Time Format Without Colon (:)

When you manually input values like **08:45 PM **into a cell, Excel automatically interprets it as time and changes its number formatting to Time or Custom.

However, inserting a colon between multiple time values could get tedious, especially when you need to input them each day or so.

And, since Excel treats a value like **1005** (also known as military time) as a regular number instead of the time **10:05**, you need to use some sort of formula for this particular case.

Example,

Here, we already have numbers without a colon. Now, we need to make some formatting changes to automatically convert a cell input like **900** into **09:00**.

For this,

- Select the cell (s) containing such numbers or where you want to input the time without a colon.
- Press Ctrl + 1 to launch the
**Format Cells**window. - Click the Number tab and select
**Custom**under the Category section. - Now, under the Type field, Replace General or any other value with
`00\:00`

and click OK.

Now, if you select one of such values and view the formula bar, you can notice that the above values (09:00) only seem like they are in the time format, but they are still numbers (like 900).

This means you cannot perform time operations like subtracting them to count the number of hours.

However, you can use a formula as a workaround method for this particular case.

**Formula:**=TEXT(C2,"00\:00")-TEXT(B2,"00\:00")+(B2>C2)

Where,

**B2**is the cell containing the clock-in time**C2**is the cell containing the clock-out time- The function
`TEXT(B2, "00\:00")`

takes the value like 800 and converts it into a text format like 08:00. **B2**>**C2**takes care of cases where the clock-in time is greater than the clock-out time while crossing the midnight time (12:00).

Here are the steps:

- Add a helper column. In our case, it’s “Total hours of work”.
- Enter the formula
`=TEXT(C2,"00\:00")-TEXT(B2,"00\:00")+(B2>C2)`

in the first cell of the helper column. Replace the cell references B2 and C2 according to your data. - Select the output cell and press Ctrl + 1.
- Then, click Number and select Time. On the right pane, select a time format like
**1:30**PM under the**Type**section. - Drag the Fill Handle to get the same result for other cells.

**Note:**After following the above steps, you will get a value that looks something like 8:08 PM. But, if you ignore the PM, you can notice that it’s actually the time difference; 8 hours and 8 minutes.