“Date” and “Time”

It is important to know, especially when working with “Date” and “Time”, how the “Date” system works in “Excel”.

As I had told you in the previous lesson, a “Date” is right aligned in a cell (1).

“Excel” “Date” system starts counting from 1 January 1900.

If a “Date” beyond this “Date” is entered, then Excel will not recognize it as a “Date” but considers it as text and aligns it to the left of the cell (2).

What you should know is that when you type in the “Date” as 01/01/1900, “Excel” gives the “Date” the value of 1. Any time you type a “Date”, it carries a value of a number in “Excel”.

Typing an example of “Date” as 02/01/1900 , gives a value of 2.
So the unit of measure between two consecutive days is 1.

Just for information. If you want to know the value of a certain “Date”, click the “Comma Style” (1) button in the “Ribbon” and the “Date” in the “Worksheet” will be displayed as number (2).

Is this important? Not at all but, what you must remember is that when you type in a “Date”, you actually type in a value and the measuring unit 1 is between two consecutive days.

Did you get this? Of course, you got it.

 

The same goes for hours. When you type in an hour, this also gets a value. What value? A value of 1/24th of a day, so 1/24th of 1

An example makes this clear.
Type as example 12:00 , which is like 12 hours, and click the “Comma Style” (1) button in the “Ribbon”, then the value 0.50 appears, so half of 1.
Same for 6:00. Here, the value .25 is displayed. Thats a fourth of 1.

Now we know how easily calculations on “Date” and “Time” are executed.

 

Adding hours

When we calculate the sum of several hours through the “Sum” function in the “Ribbon”, we see that “Excel” does not display the correct number (1). However, the correct solution is listed in the “Status bar” (2), when we select the “Range” of several hours in the “Worksheet”.

How does this work? Well, when you count hours together, by default, “Excel” will display the remaining hours. The remaining hours means the hours falling outside the unit measurement of 1, or a multiple thereof. The size of Unit 1 is a day or 24 hours.
Do you still follow?

I’ll give an example: If you have a total of 27 hours, then “Excel” will display the remaining 3 hours (27-24 = 3).
If you have a total of 50 hours, “Excel” will display the remaining as 2 hours. Here you have two time periods of 24 (50-2×24= 2). In the example below, I have a total of 47 hours and 35 minutes, so Excel gives me the result 23 hours and 35 minutes.

 

How do we solve this?

Simple….  We change the format of the cell above. Right-click the cell with the (incorrect) sum, and click “Format Cells” from the pop-up menu.

In the dialog that appears, the “Type” box enter “[h]”, which stands for hours. This will be taken into account with the hours greater than 24 hours. Click the OK button when you are done.

This is a lot better:

So whenever you want to add more than 24 hours, then enter “[h]” in the “Cell properties”.

Featured Courses

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