How to Enter Time in Excel

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

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.

1. Select the cell (s) with time values.
   
2. Press Ctrl + 1 to launch the Format Cells window.
3. Then, click the Number tab and select Time under the Category section.
4. 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.
   
5. Alternatively, choose Custom under Category and enter your preferred time format such as h:mm:ss AM/PM.
   
6. When done, click OK and close the window.

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)

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,

1. Select the cell (s) containing such numbers or where you want to input the time without a colon.
   
2. Press Ctrl + 1 to launch the Format Cells window.
3. Click the Number tab and select Custom under the Category section.
4. 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
• C2is 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:

1. Add a helper column. In our case, it’s “Total hours of work”.
   
2. 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.
   
3. Select the output cell and press Ctrl + 1. 
4. Then, click Number and select Time. On the right pane, select a time format like 1:30 PM under the Type section.
   
5. Drag the Fill Handle to get the same result for other cells.