Farihat
Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. While entering data into a worksheet, you must have used the date or time format in Excel. Time can be entered manually, but it is a strenuous process for the extensive dataset. A few keyboard shortcuts and functions can help you with this issue. This article will show you six ways to enter time in Excel. It applies to multiple cells and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Enter Time in Excel with These 6 Approaches
The article is going to introduce six approaches in Excel to use shortcuts and functions to enter time. The following discussion will cover uses of the Fill Handle icon, keyboard shortcut by Ctrl+Shift+“;” and changing Time Format. As for the functions, you will see uses of NOW, TIME, and TIMEVALUE functions. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.
1. Using Fill Handle Icon
The Fill Handle icon is commonly used to copy data to adjacent cells. You can enter consecutive time data using this method. It will automatically show you the time an hour apart. Now, the following is an example of an entry time tracker of four employees.
⬇️⬇️ STEPS ⬇️⬇️
- First, enter a time in cell C6. For example, 9 AM.
- Second, move your cursor to the bottom rightmost corner of the cell. You should get a “+” icon here.
- Third, drag the cursor down the cell till the last cell C9.
- Finally, you will get the entry times consecutively like in the following image.
2. Applying Keyboard Shortcut
This method is applicable to you if you want to enter the instant time. Time entry is easier this way since you don’t need to remember what minute or second you started or left.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell C6.
- Then, press Ctrl+Shift+;. You will track your time automatically.
- So, repeat the process for all the cells.
3. Changing Format
It may sound surprising to you but numerical data can represent time data as well. If you change them to Time format, you can witness this wonder. Following is a table that has time as numerical values. We will change its format.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select all the cells ranging from D6:D9 with their entry time in number format.
- Now, press Ctrl+1 to open the Format Cells dialogue box. You can get it on a drop-down option from the Number group, under the Home tab as well.
- Next, choose the Time format from the box.
- Then, click on the OK button.
- Finally, you will get your time data in the ways as the image below.
4. Utilizing NOW Function
The NOW function is applicable for entering instant time as well. If you convert to date and time format, it can provide you with the date value as well. Upon updating, you can change the instant time for that moment. Otherwise, it will not change and act as the result of the Keyboard Shortcut method.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell C6 to enter a date.
- Next, write the following formula on the cell.
- Now, hit the Enter key.
- After that, the running date will be visible to you.
- Therefore, repeat the process for all the cells. Here, you can see the time of “Lisa” changed, since the NOW function is active and Excel will update it with others as well.
5. Employing TIME Function
The TIME function is a combining function. You can enter the hour, minute, and second values individually in separate cells. The TIME function will combine the data in HH:MM:SS format in one cell. Following is a dataset that is for tracking total work time.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell F6.
- Secondly, enter the formula below. It will take C6, D6, and E6 as an hour, minute, and second input respectively.
- Thirdly, hit the Enter key.
- Fourthly, use the Fill Handle icon to copy the formula down the cells.
6. Using TIMEVALUE Function
The TIMEVALUE function can be used to convert numerical values into time values. To make sure their output is convenient to the users, we have added the LEFT function and the MID function with it.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D6.
- Next, enter the formula below.
- Then, hit the Enter key.
- After that, use the Fill Handle icon to copy the formula down the cells.
🔨 Formula Breakdown
TIMEVALUE(LEFT(C6,2)&”:”&MID(C6,3,2))
👉 Here, MID takes C6 as text string. It returns 2 characters starting with the 3rd character.
👉 Also, LEFT considers C6 as a text string. It shows 2 characters from the left.
👉 Finally, TIMEVALUE joins the returns from previous functions with a “:” sign in between.
📄 Important Notes
🖊️ All the data formats must be formatted properly. Otherwise, Excel may auto-format to something inappropriate for the user.
🖊️ The NOW function doesn’t get updated as the time changes automatically. Clicking on a cell with the NOW function will automatically change the previous data record to instant time data.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to enter time in Excel.
📌 Primarily, you can use the Fill Handle icon easily. A click and drag can create consecutive times.
📌 You can try the key shortcut with Ctrl+Shift+“;”. It will enter your immediate time.
📌 Also, you can change the format to Time Format.
📌 You can apply the NOW function. It will update the time as you click on the cell.
📌 You can use the TIME function. It will get you the time value only.
📌 Finally, you can get the desired result by using the TIMEVALUE function. It will convert your numerical values to time data.
Conclusion
To enter time in Excel, five methods are useful. You can use the Fill Handle icon and keyboard shortcut by Ctrl+Shift+“;”. You can add the NOW, TIME, and TIMEVALUE functions to enter time in Excel as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
