However, by default, Excel will only display hours and minutes up to 24 hours.
All data is in anExcel Tablenameddatain the range B5:E16.
The table is used for convenience only, and is not required to solve the problem.
The main challenge in this example is to correctly display time as a duration instead of time of day.
How Excel handles times
In Excel,dates are serial numbersandtimes are fractional parts of 1 day.
The times in column E are just fractional values of one day, expressed as decimal values.
This means you’re free to use standard functions like SUM and SUMIF, etc.
to sum time in various ways.
But you have to be careful about how the result is displayed.
But in cases where time is meant to show aduration(i.e.
elapsed time), the problem is that Excel will not display more than 24 hours by default.
you could see how this works in the screen below.
Time per person
To calculate time logged per person, we use theSUMIF function.
As the formula is copied down, SUMIF returns total hours per person.
The range H8:H10 has the custom time format “[h]:mm” applied.
For more information on number formats, seeExcel Custom Number formats.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
SUMIF Function
The Excel SUMIF function returns the sum of cells that meet a single condition.
Criteria can be applied to dates, numbers, and text.
