A question that is often asked is “when I add up times and the total comes to more than 24 hours, why does it not show correctly?”. To answer this, I’m using a timesheet as an example: My working day is split into 2 sessions.

To calculate how many hours/minutes I worked in the first session, I subtract the time entered in column B from the time entered in column C.

To calculate how many hours/minutes I worked in the second session, I subtract the time entered in column D from the time entered in column E.

The formula in column F adds up the results of those two calculations to give me the total time worked on each day. For example, the formula in F3 is =(C3-B3)+(E3-D3) calculates the number of hours worked on Monday.

The formulas in F9 and F10 are identical. They are both =SUM(F3:F7). So why do they display different answers?

The key is the formatting.

F10 is formatted using the Custom format hh:mm

F9 is formatted using the Custom format [hh]:mm

When you enter a formula in a cell to add up times and the result of the formula is over 24 hours, Excel displays the difference between the result and 24 hours. In other words:

The total of F3:F7 is 33 hours, 15 minutes
33 hours, 15 minutes minus 24 hours is 9 hours 15 minutes

To display the result correctly

• Right click the cell containing the date
• Select the Custom category
• Select [hh]:mm from the list of formats
• If the [hh]:mm format is not listed, create a new format by typing into the box at the top of the list 