This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
2) I have also succeeded in calculating the difference between two entered times (e.g start and stop time) - Great! (Method used was provided by the same person in this forum and is noted below.)
New 3) Now I need to calculate the Sum of the above mentioned time differences. Using normal SUM methods don't work
New 4) And from the SUM in #3, I need to also calculate an average of these time differences.
Any help is greatly appreciated as always.
Thanks, Jim (SEE FOLLOWING)
Methods used to calculate #1 and #2 is shown below:
jghans wrote:
> I'm wanting to enter military time without the colon "hhmm"
> so it ends up as "hh:mm"
Format the cells as Custom 00:00 .
To do that, select the cells, right-click and click on Format Cells, Number, Custom, and enter 00:00 into the Type field.
Note that even though the military time 1234 might appear as 12:34, it is still the number one thousand thirty-four, not the numeric time 12:34.
jghans wrote:
> I also need to be able to subtract one "hhmm" entry from
> another "hhmm" entry so I can end up with the "hh:mm"
> difference with the colon.
If the start time is in A1 and the end time is in A2, use the following formula:
=TEXT(A2,"00:00")-TEXT(A1,"00:00")+(A1>A2)
formatted as Custom hh:mm .
This results in true numeric time.
The term (A1>A2) handles the case when the start time appears to exceed the end time because the time interval spans midnight.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Summing the time differences should work, but you should format the cell with the sum formula with the custom format [h]:mm or [hh]:mm. The square brackets [ ] around h or hh specify that the time is cumulative time (which can be higher than 24 hours), not clock time.
Averaging should work too.
Answer accepted by question author
Jim wrote:
> 3) Now I need to calculate the Sum of the above mentioned time
> differences. Using normal SUM methods don't work
It should, if you are computing the intermediate differences in a cells.
Suppose that A1:A100 contains start times and B1:B100 contains corresponding end times. Then put the following formula into C1 and copy down through C100:
=TEXT(B1,"00:00")-TEXT(A1,"00:00")+(A1>B1)
formatted as Custom hh:mm.
Then the sum is simply =SUM(C1:C100), and the average is simply =AVERAGE(C1:C100).
Both should be formatted as Custom [h]:mm because presumably the sum of the hours can exceed 23.
However, if you want to avoid computing the intermediate differences in column C, you could do this:
=SUMPRODUCT(TEXT(B1:B100,"00:00")-TEXT(A1:A100,"00:00")+(A1:A100>B1:B100))
formatted as Custom [h]:mm.
2a. If #1 is computed in C1, then =C1/100 or =C1/COUNT(C1:C100).
2b. If you would like to avoid the intermediate sum, use the following array formula [*]:
=AVERAGE(TEXT(B1:B100,"00:00")-TEXT(A1:A100,"00:00")+(A1:A100>B1:B100))
In both cases, remember to format as Custom [h]:mm or [h]:mm:ss.
[*] Enter an array formula by pressing ctrl+shift+Enter instead of just Enter. Excel will display an array formula surrounded by curly braces in the Formula Bar, i.e. {=formula}. You cannot type the curly braces yourself. If you make a mistake, select the cell, press F2 and edit if needed, then press ctrl+shift+Enter.
Great - You all do a super job! Thanks so much.
Excellent! - Thanks Joe - Problem Solved!! - - You guys are awesome.