Do you want to calculate the number of hours and minutes it took you to complete a job in a month or a week? Well, use good old Excel . How do you say? Have you tried but the sum starts from zero every time you exceed twenty-four hours? Obviously, if you don’t set the program right.
Using the hours calculation function present in Excel, you can sum various amounts of time (hours, minutes and seconds) by writing them in different cells of the spreadsheet (e.g. each dedicated to a day of the month or week) and obtain the result automatically, just set the right options. If you want to know what I mean and you want to find out how to add up the hours in Excel , don’t wait any longer and read the instructions I am about to give you.
For the tutorial I will use the 2021 version of Office, which is available for both Windows and macOS, but the procedure to follow is valid for all versions of Excel and all operating systems, including Excel Online and the Excel app for smartphones and tablet. So, what are you doing still standing there? Roll up your sleeves straight away and find out how to solve your “chronological” problems with Microsoft Excel: everything is explained below!
Index
- How to add hours (and times) in Excel
- How to sum Excel hours over 24
- How to add negative and positive hours with Excel
- How to sum the hours in Excel by calculating the difference between several times
How to add hours (and times) in Excel
First, launch Excel via its quick launch icon and start creating a spreadsheet whose cells contain all the times you intend to add up. Times must have this format: hh: mm: ss (e.g. 13:00:00 to indicate thirteen hours, zero minutes and zero seconds, or 13:00:00 if you want to add times).
Let’s take the practical example for which you want to know how to sum the working hours in Excel : you can create a table in which the days of the week are listed in the cells ranging from A1 and A7 (eg Monday, Tuesday etc.) and in those ranging from B1 to B7 the number of hours, minutes and seconds you have spent to complete the work you want to report.
Once you have listed all the times you want to add up, you need to create the cell in which the total of the sum of the hours should appear. To do this, click on the cell you want to dedicate to the total (eg D1 ) and fill in the formula = SUM . Specifically, if you want to add the number of hours specified in the cells ranging from B1 to B7 , you need to type the formula =SOMMA(B1:B7)and press the Enter key on the keyboard. Simple, am I right?
How to sum Excel hours over 24
Through the procedure indicated in the previous chapter you have been able to add the hours but, unfortunately, the number resulting from the sum formula does not coincide with what it should actually be. The problem lies in the fact that, after 24 hours, the format applied to the time is wrong and, therefore, you have to manually force the correct one.
To do this, click on the cell with the total , expand the Custom drop-down menu located in the Home tab of Excel and select the item Other number formats from the latter. In the screen that is shown to you, in the Custom category , choose the one represented in the form [h]: mm: ss . If you ever do not find it, use the Type text field at the top to type it manually and then press the OK button at the bottom.
Just to clarify, the [h] format allows you to show the elapsed time in hours, overcoming the problem of calculating for values over 24 hours, while that hh or h simply expresses the time. You can understand that, in case of need, you can use the parameters [m] and [s] respectively to calculate the elapsed time in minutes and seconds.
How to add negative and positive hours with Excel
If you intend to subtract hours, but the result should be negative, know that Excel will prevent them from being displayed (only a long series of the # symbol is shown ). In fact, on Excel it is possible to add and subtract the hours, showing only the positive result, as I explained to you in a previous chapter .
Showing the time of a subtraction operation whose result is negative means forcing its display, converting the result into text. A possible formula to use would be =SE(B1<A1;“-” & TESTO(ASS(B1-A1);”HH:MM);B1-A1)where instead of B1 and A1 the cells containing the hours should be indicated, assuming that the subtraction formula is B1-A1 .
The formula in question will simply set the minus sign on the result of the subtraction, setting it as text in the visual format of the time.
But now in case you want to take this negative hour value and add it to another hour value (whatever it is, positive or negative), the sum formula will give you an error. This is because of course you cannot add values and plain text (the negative time is in text format). How to proceed then?
The only operation you have to do is to carry out a sum normally, even if the cell of the presumed negative value is visible with only # symbols and, therefore, without forcing its display with a negative sign (as I explained to you in the paragraphs This is because Excel has calculated the value anyway, in this case negative, but it simply does not show it.
A sum formula between positive and negative hours can therefore be carried out as I explained to you in the chapter on the sum of hours and in the one for the sum over 24 hours , without having to carry out any particular operation.
.
How to sum the hours in Excel by calculating the difference between several times
Have you created an Excel sheet in which you note the times when you start and finish working on your projects each day? So let’s see how to calculate the difference between all the times and obtain a sum of all the hours of work carried out.
In order for Excel to perform its calculation, you must format the cells of the worksheet properly. Therefore, proceed to select the cells in which there are the start and end times of the work, expand the Custom drop-down menu located in the Home tab and select the item Other number formats … from the latter.
In the window that opens, select the Custom item from the left sidebar and indicate in the Type text field the formatting you have decided to adopt for the start and end times of the work. In my example I used dd / mm / yy hh: mm , which equates to a date and time like 08/08/16 12:00 .
Once you have selected the correct formatting, or rather, the formatting that reflects the format in which you have written dates and times in your spreadsheet, click on the OK button to save the changes.
Next, go to the cell where you want to view the total number of hours you have worked and type the following code =INT(((SOMMA(C2:C7))-(SOMMA(B2:B7)))*24)where instead of C2: C7 and B2: B7 you have to enter the coordinates for the cells in which you have listed the start and end times of work.
The procedures illustrated in the tutorial apply not only to Excel in the desktop version (both on Windows and on macOS), but also for the mobile version of the application available on smartphones and tablets. The formulas to be used are always the same.
I hope I was clear enough with my examples. In any case, if you still have doubts about how to add the times in Excel or how to calculate the difference between two or more times , you can consult the Microsoft website where there are numerous examples on both topics.