Time in Excel
When calculating time, use normal formulas i.e. =A1+A2, etc
However, if the resulting sum exceeds 24 hours, you get some strange results. The way to correct this is to format the cell using CUSTOM formatting.
Set the cells holding the time(s) to normal hh:mm format, but in the cell where you want to calculate the totals set the cell format using custom to [h]:mm. This will correct the 24 hour problem.
Try this;
Put 7:45 into 4 consecutive cells (all formatted as hh:mm) and SUM the values. If the target cell (SUM) is formatted as hh:mm the answer will be 7:00
That's because the 4x7:45=31 hours
Because that is greater than 24 (hours), the difference is 7 hours (the answer).
If you change the target cells format to [h]:mm, it will now correctly answer 31:00 (31 hours)
|