__

This video series shows you how to effectively calculate with time in spreadsheets such as Excel, Openoffice or Libreoffice.

Working with time in spreadsheets is quite simple yet can be confusing for someone dealing with the matter for the first time.

Two different time formats exist:

1. Point in time such as 8:15 am or 9:30 pm

Points in time are limited in size. Hours go to maximum 23 then revert back t0 0. Minutes go up to 59 and then start back from 0. Ditto for seconds

Hours minutes and seconds get formatted using h, m and s respectively.

If the time we're formatting is 8:09 in the morning:

h:mm am --> 8:09 am

h:m --> 8:9

hh:mm:ss --> 08:09:00

2. time segments. These are time durations such as 8 hours 15 minutes

In contrast to points in time, time segments do not have any limits. Yes I can watch TV for 29 hours or sleep for 920 minutes.

For the spreadsheet to correctly format your time segment use two square brackets [ ] for the first item in your format.

If your wish to display the time segment 31 hours and 15 minutes in:

hours and minutes --> use [h]:mm --> 31:15

Brackets around h ensure that h does not go back to zero beyond 23 hours

just in minutes --> use [m] --> 1875

in seconds --> use [s] --> 112500

You also don't have to live with the “:” as a time separator. You can use any other sign. You can also add any text you wish to your time by including that text in double quotes.

Important note

To change the duration form let's say hours to minutes, you do not have to modify your formula. All you got to do is the format from [h] to [m].

Spreadsheets save times as decimal numbers. 24 hours = 1. Any time is a fraction of that. So 12:00 or 12 hours is 0.5 and 6:00 or 6 hours is 0.25 and so on.

Spreadsheets work with those underlying decimal numbers and not the times you see. That's why the format of those times is not relevant to the calculation.

Pay attention to your rates when combining time with real number

If you are using for instance hourly rates like say costs/hour, miles/hour: You have to enhance your calculation formula with 24.

ie

hourly rate = 50 is in cell B2

hours works = 6 is in Cell A2

Invoice amount = B2 * A2 * 24

Were my rates in minutes then I would have to enhance my formula with 24 and 60

It would look like this: = B2 * A2 * 24 * 60

These corrections or enhancements to your calculation formulas are necessary, because as discussed in part 2, spreadsheets treat time as a fraction of a day. Six hours is thus 0.25.

So in the example above just multiplying B2 * A2 would yield 0.25 * 50 = 12.5

Another calculation sample to realy drive inthe point.

Please use the feedback link below for comments and suggestions.