EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Formula and Functions Excel Sum Time
 

Excel Sum Time

Madhuri Thakur
Article byMadhuri Thakur

Updated August 9, 2023

Sum-Time-in-Excel

 

 

Sum Time in Excel

  • Suppose you have been provided with two-time values and want to subtract or add the time in Excel so the question is, how will this work? Does it require anything extra out of the box? Or this is the same thing we do with numbers.
  • The answer to the above questions is that this subtraction and addition of time in Excel will work the same as with numbers.
  • The difference between these time addition and the addition of the normal number is the format of numbers for that particular cell; mostly, even that doesn’t matter.
  • We will see in the following examples that the format of the result of an addition/subtraction makes it very useful to do this thing in Excel.
  • Here we need to understand that doing this calculation in Excel has the benefit of getting the results in the required format.

How to Sum Time in Excel?

Let’s use a few examples to sum time in Excel.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this Sum Time Excel Template here – Sum Time Excel Template

Excel Sum Time – Example #1

  • For our first example of this topic, we will try to cover the basics.

 How To Sum Hour In Excel Example 1-1 

  • As per the above screenshot, suppose we have the employee’s in and out time data.
  • We want to calculate their working hours by subtracting the out time from time; as you can see, we have applied the simple subtraction formula in the cell.
  • After applying the formula, we will get a result shown in the screenshot below.

How To Sum Time In Excel Example 1-2

After that, we will apply the same formula to other cells by dragging the D2 cell.

How To Sum Time In Excel Example 1-3

  • In this example, the time is formatted as HH: MM format as it’s most widely used. Also, it’s in 24 Hours format by default; otherwise, we can format it by indicating AM / PM in 12 Hours format.
  • From the screenshot, we can see that Ritesh’s in time is 08:55 and out time is 18:01, so he spent 9 hours and 5 minutes that day in the office.
  • So that’s the most basic use of time subtraction/addition.

Excel Sum Time – Example #2

  • Here let’s see an example where you have to change the format of the cell to get a justified/realistic result,

How To Sum Time In Excel Example 2-1

 Example 2-2

  • You can see from the above screenshots that when we added times 16:42 and 20:12, the results differed from what we got here.
  • So, why does that happen? This happened because the format of the time is in 24 hours by default.
  • Now to get the actual results in hours, we have to change the format of the result cell.
  • To change the format of the cell, there are two ways to do so as follows.

Method: 1

Go to the Home tab, click the Cells group, and then choose the Format and format cells afterward.

 Example 2-3

Method: 2

Right-click on the cell, then click on format cells.

Example 2-4

  • After that, the steps are the same for both methods; click Custom in the category.
  • Now type our required format of cell – [h]: mm;@ and then click ok.
  • Do remember that we have to type colon after [h] and a semicolon after mm
  • Look into the below screenshot for your reference.

Example 2-5

  • So after applying this format to our result cell, we shall have our supposed result. Find the screenshot for reference below.

Example 2-6

Excel Sum Time – Example #3

  • Now that we have understood the basics, let’s move to some useful features Excel brings to these time calculations.
  • In this example, suppose we have data of students who took the time to complete two laps in a race organized by the school as follows.

How To Sum Time In Excel Example 3-1

  • So here, as per the above screenshot, we have data for the given students.
  • Also, we have made a simple addition to both laps.

Example 3-2

  • After the addition of the available data, we should have the data as follows:

 Example 3-3

  • So now here we have the data which can decide the winner undoubtedly, but we can make this data interesting by changing the format of the result cell.
  • At present, the cell is in hh:mm: ss.
  • Now suppose here we want to see only hours in the result cell; we have to follow the same procedure to change the cell format as we have done before, shown in the screenshot below.

Example 3-4

  • So select the cells, right-click with the mouse, select format cells, click Custom, and type [hh] under the Type section.

Example 3-5

  • After applying the same, we should have the result cell as per the screenshot below, and we can see that each runner has taken 2 hours, so this doesn’t turn out valuable for this data.

Example 3-6

  • Now, if instead of hours, we want the result in minutes and seconds, so while changing in format instead of [hh], we will make the custom format [mm] for minutes and [ss] for seconds, respectively, then we should have data for minutes and seconds as below.

Example 3-7

Example 3-8

  • The above screenshot shows the time taken by a particular runner in minutes, which narrows the winning candidate, so even if some candidates took the same minutes, we need more accurate data than the minutes. So we should take seconds.
  • As per the below screenshot, we can see the time taken by the runner to complete both laps in seconds.

Example 3-9

Example 3-10

  • So the winner can easily decide by the fetched data in seconds format.

Things to Remember About Sum Time in Excel

  • As we discussed, these time calculations mostly depend on the format you choose for your result cell; if you are calculating the time for a race, you should choose the second format to get the time in seconds. And if we want to calculate the employees’ working hours, we should choose the hour format.
  • So we should use a suitable format for these calculations
  • As we have seen in the above examples, we can use this calculation with the general logic of subtraction and addition, but in the above examples, we have mostly taken 2 or 3 subjects, but when we would have more subs, we can use =sum or =substitute
  • The time calculation is mostly dependent on how you format the result cell.

Recommended Articles

This is a guide to Sum Time in Excel. Here we discuss How to Sum Time in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. SUM Function in Excel
  2. How to SUM in Excel
  3. Time Function in Excel
  4. VBA Time

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Sum Time Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Sum Time Excel Template

EDUCBA

डाउनलोड Sum Time Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW