Excel calculation: Calculating with time

YOUR OBJECTIVE


You want to calculate how many hours have elapsed between two given times.

In this learning module you will learn how to perform excel calculations with time and some things to consider when doing so.

 

HOW DOES EXCEL CALCULATION WITH TIME WORK?

In Exel calculation function time is saved in as a numeric fraction . The number “1” stands for 24 hours (one full day); “0.5” is 12 hours (half a day); “1.5” would be 36 hours (one-and-a-half days); “0.25” would be 6 hours.

So that the Excel calculation function will recognize your input as time, you must use a colon to separate hours from minutes. Using this pattern, “5:30” stands for 5 hours 30 minutes. With an additional colon you can enter (or display) the number of seconds, i.e., “1:12:25” means 1 hour 12 minutes and 25 seconds.

CAUTION

Be aware that Excel calculation cannot calculate with negative times. It is only possible to calculate the number of elapsed hours from 22:00 (10 pm) until 06:00 (6 am) the next day in two steps: 22:00 to 24:00 and 0:00 to 6:00.

In the following example, the number of hours worked is to be calculated and summarized. To do so, the Work beginning time is subtracted from the Work end time, and the time spent on Lunch break is deducted, too.

Now, if you add up the number of hours worked for of all days of the week yourself (in this example: E4 to E8), you will immediately see that the result (in E9) is incorrect.

The reason for this is that the format applied to E9 was the format for time of day, a format that cannot display any value over 24:00:00 (24 hours). If you use this formatting, adding 17:00 to 18:00, for example, would return a time-value of 11:00. Using American or English style times (5:00 PM and 6:00 PM) lead to the same problem.

If you want to add up the values for hours worked and get a reasonable, i.e., correct, result, you have to format the results cell (E9) differently. On the Home tab, in the Numbers group, click the dialog box launcher. The Format Cells dialog box will open:

In the list of various time formats, some begin with a 1 (i.e., 13:30:55 or 1:30 PM) while others begin with a 3 (i.e., 37:30:55). These formats return different results. In the following example, the times 17:00 and 18:00 (5 PM and 6 PM) are added together. Depending on the formatting of the destination cell, different results are displayed:

The upper result, formatted using the 13:30:55 format, returns the result 11:00:00. This is the result you receive when you add 18 hours to 17 hours and subtract 24 hours. This is an incorrect result.
The lower result, formatted using the 37:30:55 format, displays the total number of hours that result when you add 17 hours (5 PM) to 18 hours (6 PM), namely 35 hours. This is the correct result.
Any time format that begins with the number 1, is intended to display the time of day; formats that begin with the number 3 are intended to show the number of hours.
Therefore, you should display times of day with a 1 format and durations, i.e., a total number of hours, with a 3 format.

Excel Calculation: HINT

Some time formats offered by Excel calculation function express time as hours:minutes:seconds. If you don’t want to display the seconds, as is often the case, you can create a custom format to exclude them. To do so, first format the cell with a predefined time format that you basically like. As an example, the contents of the cell might look like this:

Now (on the Home tab, in the Numbers group) click the dialog box launcher. Then, in the Format Cells dialog box, select the Custom category.

In the Type entry box, the current format

h]:mm:ss;@” can be seen. (The @-character implies that this is not a time.) When you delete the characters “:ss” and then click OK, the hours in your results cell will display without seconds. See E9 in the next illustration.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen