Erm's I.T. Girl – Zelna Ellis

Don't fear when Zel is near…

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.
Time Calculations

6 March 2009 - Posted by | Spreadsheets |

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: