Dates and times in Excel are stored as numbers in the back end. But, you can easily use simple arithmetic operations and formulas to tally time in Excel.
In this article, we learn how to tally time in Excel without an error or wrong calculation.
How to tally time in Excel
When adding up all the time values in Excel, we use the SUM formula. Here are the steps to follow:
Let’s say you have two tasks, and you need to know the total time it will take to complete both tasks. In cell B2, task 1 takes you to 6:45, while in cell B3, task 2 takes you to 9:30. To tally up the total hours taken to complete the sum, you will use the formula =SUM (B2:B3) and press Enter.
You will get the result as 16:15 in cell B4: 16 hours and 15 minutes which was the total time taken to complete the two tasks.
Apart from the SUM function, you can also use the AutoSum function to tally up time in Excel.
To do this:
1. Start by selecting the cell where you want your results displayed.
2. Click on the Home tab on the main menu ribbon.
3. In the Editing group, click on the drop-down arrow next to the option AutoSum.
4. Select Sum.
5. Press Enter key on the keyboard, and you will get the same results as above.
How to add up more than 24 hours in Excel
Using our example above, in cell B2, type the time as 12:45, while in B3, type as 15:30.
1. To get your results, type in cell B4 =B2+B3
2. Press Enter. Here, you will get the result at 4:15.
3. Therefore, to display your time as more than 24 hours, select the results cell
4. On the main menu ribbon, click on the Home
5. In the Cells group, click on the drop-down arrow under Format.
6. On the displayed list under Protection, click the option ‘Format Cells’ to display a pop-up box.
7. In the Format Cells box, under the Category list section, select Custom.
8. At the top of the list of Formats, in the Type box, type [h]: mm;@
9. Click OK. You will get your results in 28 hours and 15 minutes.
You can also use the keyboard shortcut Ctrl + 1 to open the Format Cells dialog box.
How to subtract time from another in Excel
You can use the simplest and most obvious formula when calculating the time difference in Excel. This is:
=End time – Start time
With the above formula, you will get the difference between the two times. In case you are dealing with AM and PMS, after subtracting, click Ctrl + 1 keys on your keyboard to display the Format Cells box.
In the dialog box, click the Custom option under the Category list.
In the Type box, click the option h: mm, which represents hours and minutes.
Click OK. Afterward, you will get the time in hours and minutes instead of AM or PM.
When you are dealing with time that’s more than 24 hours,
Follow the steps below when subtracting your time.
1. Open the Format Cells dialog box.
2. In the Type box, type in m/d/yyyy h:mm AM/PM
3. In your Excel worksheet, type in your date entries following the formula month/day/year. Then write time using ‘a’ or ‘p’ for AM and PM.
4. To get your results, subtract the two dates and multiply by 24 hours.
Similar Reads:
How to Turn off Excel Date Formatting: 5 Easy Ways
How to preview the Excel worksheet
Conclusion
In this article we have seen different ways to tally hours, minutes and seconds.