Taking into account time in Excel: addition and subtraction inequality between hours and minutes

The previous class we checked that the date in Excel is the number of the number of days passed from 01.01.1900, and now we can see that the time in Excel is the fractional part of which is a period of days.

Working with the time is a common thing. For example when the personnel department has to generate a report on the balance of the working time of employees. Production department needs to calculate how many hours will require work on the milling machine parts manufacturing small-scale production. Time should be considered by the engineers to analyze the performance of equipment in certain number of operations.



How to calculate the sum of the time in the Excel?

Each fractional part of the numbers in Excel can be displayed in hours and minutes (depending on the display format).

This fact is the best to learn by a specific example:

  1. Complete range of cells A1: A3 by the numerical values: 0.25 (a quarter of the day); 0.5 (noon); 0.75 (3/4 day).
  2. Select A1: A3 and using the «Format Cells» (CTRL + 1) of the dialog box, specify the format of «Time», as shown below:
Format Cells.

To add time to in Excel by formula or function: =SUM() it is necessary to use a special format.

  1. In cell A4, type: =SUM(A1:A3). As you can see, instead of 36 hours, we see only 12 - it's the next day. To display the amount of hours we change the cell format.
  2. Open the box «Format Cells»-«Number»-«Category»-«Custom». In the field «Type:», enter: [h]:mm:ss. And click OK.
Custom.

Now the number of hours and minutes summarized is displayed.



How to calculate the time difference in the Excel?

This time display method is very useful. Let's say we need to find the difference between the time in minutes. In cell B2 enter the formula: =A2-A1. Install now for the cell «Custom» and this time in the field «Type:», enter: [mm]. And click OK.

Time in minutes.

So we managed to transfer the time in minutes. If we are in the same cell type 1 (or the time 24:00, which is essentially the same), we know that in the day 1440 minutes.

Note. With the help of cell size, we set up a way of displaying their values. For example, you can add or remove time from the date in Excel.

Accounting the time during the day

In the following example, we calculate what part of the day was held from midnight to 9:45.

  1. In cells A1: A3, type 9:45.
  2. Go to the A1 and call the box «Format Cells» (CTRL + 1). In the «Number» from the «Category» tab, select the «Fraction».
  3. Go to the A2 and set the same way «Percentage» format (or just press the hot key combination CTRL + SHIFT + 5).
  4. Go to the A3 and remove the cell format. As you know from the previous lessons, to delete the format is to assign a format the default «General». To do this, you can press the key combination CTRL + SHIFT + (~) or use the method described above.
Types.

As you can see 9:45 is 2/5 parts of the day. More simply, it took until 40.63% the day.

How to put the current time in Excel? Automatic input of the current time in Excel is performed by hot key combination CTRL + SHIFT + (;). Automatic insert of the current date and time is different in Excel in using the SHIFT key.

All Classes

en ru