“Time” subtraction

The simplest way for calculating Time differences is to subtract “Time” from one cell to another. C3-B3 in the example below. Easy but not very useful.

Usually, an employee will have a lunch break.

As an example I give Peter a lunch time of 1 hour.

You must then subtract 1 in the “Formula”. Not really because 1 stands for 1 day or 24 hours. So you need to deduct 1/24th .

So the “Formula” is C3-B3-1/24 .

When you type in this way, “Excel” will calculate it correctly, but there are two drawbacks. One, this is not very clear when someone else who is using this “Formula” would read it and two, what do you type when the lunch is only three quarters of an hour?

It is better when you subtract the minutes in the “Formula”.

For this, type the “Formula” as = C4-B4-“0:45”. Do not forget to place the number of minutes between parenthesis.

 

Calculate Wages

If you want to calculate the wages for the hours worked, multiply the hourly wage by hours worked, multiplied by 24.
Why multiply with 24? Because “Excel” treats time differently than numbers. That’s the only explanation I can give you.

 

Suppose you do not want to calculate wages hourly but instead by minutes. To do this, add another 60 to your “Formula” as there are 60 minutes in an hour.

 

“Date” Functions

A first feature that I’m going to discuss here is the NETWORKDAYS function.

This function does exactly what it says.

It calculates the number of workdays between two dates.

 

We start a function as always with the equal sign (=).

We type ‘NETWORKDAYS ( ‘

The first argument that we must enter is the start date, in this case C1

We type a comma , and the second argument is the end date, C2.

We type a comma again ,

The third argument is the holidays, this is optional.

It is impossible for “Excel” to know when you are on leave or what the public holidays are.

So this info goes in a list, and this list or at least a “Range” of this list, has to be given as the third argument.

Click Enter on your keyboard, and “Excel” calculates the number of working days.

 

The second example is the function DATEDIF.

This function calculates the difference between two dates.

For example, I want to know how old I am in days.

 

I type the equal sign, and type DATEDIF, uppercase or lowercase letters that does not matter. I open the brackets, and as the first argument I give my date of birth, this is in cell A1. Next I specify the second argument, or today’s date. This in cell A2.
The third argument I give is how I want to show the result. Y years, M for months, and D for days. Place this last argument in quotation marks.

In my case I want to know the number of days, so I type “D”, and click Enter.

 

Featured Courses

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