Calculating with Time – Part 1

At the request of Etienne Baetsleer and a whole lot of others, I will show you how to do calculations with Time.

What we need to know when we start doing calculations with Time is that, Time does not exist in Excel.
Indeed, this is the same as date. Excel does not recognize dates and Excel does not recognize time.
A date is a number. I actually discussed this in tip 48.
A date is an integer and a timestamp is a decimal number.Let me make it clearer.
When you enter a date in a cell, eg 15/01/2010. This is a date for you.
For Excel, it is a number.
What number?
The number is 40193.
How does it get this number?
Excel starts from the date 01/01/1900. This date, therefore, has the number 1.
So if you enter the date 02/01/1900, and you give that cell the “Numbers” format, the number 2, will be displayed in this cell.
The date 15/01/2010 is therefore, the number 40193.
This is very easy to calculate, right! What a feeble joke.

Ok, now we know that one day is equal to 1, and we know that a day consists of 24 hours.
Then, we know that an hour is equal to the decimal number 0.0416667.
How did you get this?
1 divided by 24 = 0.0416667
And 8 hours is 1/3 of a day, so it has the decimal value 0.3333333.
This can be calculated mentally. Right?

By default, a time is displayed as hours: minutes: seconds.
You can customize this format when you remove all the excesses in the “Format Cells” dialog box.

Now that we know all this, it’s a cinch to deal with time calculations.
First the simplest one.
To know how many hours have elapsed between two time periods, separate the two hours.
Thus, in this example, it is the end-hour (D2) minus the start-hour (C2).
This indicates the number of hours worked (E2).

Now, if you want to calculate the salary you might think that I multiply the number of hours with the hourly wage, to get the total salary.
I said that Excel treats one hour as a number, and what was that number?
1/24 of a day.

So we multiply the product of two numbers with 24.
Then, we get the correct wage.
Do not forget to set the number format as “Number” or “Currency” (A).

The same rule applies when we want to count the total number of hours.
We can multiply this sum by 24.
Also, do not forget to set the number format as “Numbers”.

We continue in Part 2

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats