Spreadsheets: Time Calculations

How to calculate the hours between two different times. E.g. Between the Starting Time and the Finishing Time of a race.

If you have done spreadsheets calculations this looks simple: Finishing Time – Starting Time. =B2-A2
Well, it is not so simple. If the start time is on the same day as the end time (i.e., Start at midnight and end at 5:00 AM), it’s fine. But if they started work at 8:00 PM and finished at 2:10 AM the next morning, it needs to know the end time is on a different day.

There are a few methods this can be done. The easiest is way is to add 1 to The Answer if the Starting Time is smaller than the Fininshing Time. =B2-A1+IF(A2>B2,1). Otherwise if it is over a couple of days, the entire date must be entered in the starting time and finishing time.

Now you want a total. Using the SUM formula is totally correct, =SUM(C2:E2), but renders a wrong answer due to the FORMAT.
Select the column containing the Time Taken Formula. Right-Click then Format Cells, Numbers, Custom, hh:mm. If you still get a wrong answer you can choose/type [hh]:mm in the Custom text box. The square brackets tells the program to use elapsed time rather than time of day.

The following figure demonstrates the differences between the formulas and between the different formats.