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