Mahbubur Rahman
I’ll explain to you how to add time in Excel over 24 hours in an efficient and easy way. We will use a sample dataset, where names and weekly hours are given.
Download Practice Workbook
4Â Ways to Add Time in Excel Over 24 Hours in Excel
Method 1: Adding Time in Excel Over 24 Hours Manually
We will see, how to add time over 24 hours manually in this method.
First, click on cell H5 and type the following formula.
=C5+D5+E5+F5+G5
Now, press ENTER key.
After that, drag down to AutoFill.
Here, we can see, it is not the result we wanted. Because Excel can not count time over 24 hours. That’s why we need to format the cells.
At this point, left-click the mouse button and select Format Cells.
A new dialogue box will pop up, from here, we will select Custom and [h]:mm. Then click OK. Here, we want our time count in Hour and Minute format not in Hours:minutes: seconds. That’s why we didn’t select hh:mm:ss.
Finally, we got our result. It looks like the image below.
Read More: How to Add Hours to Time in Excel (8 Quick Ways)
Method 2: Adding Time in Excel Over 24 Hours Using Sum Function
This time we will add time using the SUM function.
First, click on cell H5 and type the following formula.
=SUM(C5:G5)
Now, press Enter key.
After that, drag down to AutoFill.
we can see, it is not the result we wanted. Because Excel can not count time over 24 hours. That’s why we need to format the cells.
At this point, left-click the mouse button and select Format Cells.
A new dialogue box will pop up, from here, we will select Custom and [h]:mm. Then click OK. Here, we want our time count in Hour and Minute format not in Hours:minutes:secornds. That’s why we didn’t select hh:mm:ss.
Finally, we got our result. It looks like the image below.
Read More: How to Add Hours, Minutes, and Seconds in Excel
Similar Readings
- How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
- Subtract and Display Negative Time in Excel (3 Methods)
- How to Sum Time in Excel (9 Suitable Methods)
- Add Milliseconds to Time in Excel (with Quick Steps)
- How to Add Minutes and Seconds in Excel (3 Easy Methods)
Method 3:Â Adding Time in Excel Over 24 Hours Using AutoSum Function
Now, we will use AutoSum from Formulas ribbon.
First, click on cell H5 and go to the Formulas Ribbon, then select Sum from the AutoSum section.
Now, our worksheet will look like the following image.
Press ENTER key now.
Drag down to AutoFill.
We can see, it is not the result we wanted. Because, Excel can not count time over 24 hours. That’s why we need to format the cells.
At this point, left-click the mouse button and select Format Cells.
A new dialogue box will pop up, from here, we will select Custom and [h]:mm. Then click OK. Here, we want our time count in Hour and Minute format not in Hours:minutes: seconds. That’s why we didn’t select hh:mm:ss.
Finally, we got our result. It looks like the image below.
Read More: Adding Hours and Minutes in Excel (4 Suitable Methods)
Method 4:Â Adding Time in Excel Over 24 Hours Using TEXT & SUM Function Together
Here, we will see how to use the SUM and TEXT function together to add time over 24 hours.
First, click on cell H5 and type the following formula.
=TEXT(SUM(C5:G5),"[h]:mm")
Now, press ENTER key.
Finally, drag down to AutoFill rest of the series.
Simple.
Read More: How to Add Time in Excel Automatically (5 Easy Ways)
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.
Conclusion
There are four different ways to add time in Excel over 24 hours. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback. You may also browse this site’s other Excel-related topics.
