Entering time manually is one of the most boring things to do in Excel. Luckily, Excel has some features by which you can easily input time value in your database without wasting your work hour. Today in this article, we will learn some of the methods to enter time in Excel.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Enter Time in Excel (5 Methods)
1. Enter Current Time in Excel
There are many ways to enter the current time in your worksheet. We will learn two of the easiest ways to do it.
i. Using Keyboard Shortcut to Enter Time
In the given example we have a dataset containing only the names of some candidates. We need to enter the current time in the “Start Time” column by using the keyboard shortcut.
Select cell C4. Press “CTRL+SHIFT+; (Semi-colon)” to enter the current time.
Now copy them down to the end of the dataset.
If you want to change the time format to hh:mm:ss or hh:mm:ss AM/PM you can do it easily. Go to the Number Format Ribbon and click on this icon to show all formatting styles.
In the new window, you can choose your suitable time formats. We have chosen the hh:mm:ss AM/PM format. Click Ok to apply this format.
And our format is successfully applied.
ii. Using the NOW Function to Enter Time
We can also use the NOW function to display the current time. In cell C4, apply the NOW function. The function is,
Press Ok to get the current time.
The result is in date and time format. Now we can easily change the format by following the procedures that we discussed in the previous method. To do this, open the number formatting window. From there, choose a suitable time format for you.
Click Ok to apply the format.
Now simply copy down the value to the end of the column. That’s how you can enter the current time.
2. Using the FILL HANDLE Feature to Enter Time
In the following example, names of some workers are given and their “Start Time” and “End Time” columns are given. Every worker starts his/her work one hour after the previous worker. We have to enter their starting time and ending time using the Fill Handle feature.
The first value for “Start Time” and “End Time” is given. Now click on cell C4. Move your mouse to the bottom right corner of the cell until you see the fill handle icon (+).
Now double click on the fill handle icon to get the time value serially.
Similarly, we can do the same in the End Time column. Select cell D4 and get the Fill Handle icon.
Double-click on the icon to get the result.
3. Changing Format to Enter Time
We can enter time by changing the number format of the cell. Let’s see!
In the following example, we are given the time value of some candidates in number format. We will use the formatting options to change those numbers into time.
To do it, go to the Number Format Ribbon, click on this drop-down icon to open formatting styles. Choose the time formatting.
And our values have turned into time values.
Also, you can change the format from the formatting style window.
Click Ok to get your format.
4. Using the TIME Function to Enter Time
In the given example, “Hours”, “Minutes”, and “Seconds” are given for some workers. We will have to find their “Reporting Time” using those values.
In cell F4 of Reporting Time column, apply the TIME function. Insert the values and the final form is,
Where, C4, D4, and E4 are the values for hours, minutes, and seconds.
Press Ok to apply the function.
Do the same for the rest of the cells.
5. Using TIMEVALUE Function to Enter Time
In the following example, time values for some candidates are given. We will use the TIMEVALUE function to get the time from those values.
In cell D4, apply the TIMEVALUE function. Insert the values and the formula is,
C4 is the time value cell and 2 is the number of characters we choose for the LEFT function. For the MID function, we start from the 3rd character and the number of the selected characters is 2.
Press Enter to get the result.
Now apply the same formula to the rest of the cells of the column.
Things to Remember
➤ The results of the NOW function changes only when the worksheet is calculated. The date and time that are used are taken from the computer’s system clock.
➤ Change the number formatting of the columns before entering time values.
Entering time values in excel is quite easy but you have to keep your eye on some conditions to get the job done. We hope this article proves useful to you. If you have any confusion or suggestions, you are most welcome to comment.