How to calculate the amount of hours in Excel. How to calculate time in excel
(in the fractional part of the number). For example, 0.75 corresponds to 18:00, 0.5 to 12:00. If, for some reason, TIME values \u200b\u200bare stored in decimal form, such as 10.5 hours, then they can be converted to standard time format (hours: minutes: seconds). The article also shows you how to convert 1800 to 18:00.
If the cell contains the number 40638.5, then changing accordingly Cell format you can display it as 04/05/2011 12:00:00 (40638 corresponds to 04/05/2011, and 0.5 corresponds to 12 o'clock, see the article for more details). To do this, call the Dialog Box Cell format, pressing CTRL + 1, select number format The date, then select in the box A type - format 03/14/01 13:30. Also, using the Cell Format, the number 40638.5 can be displayed as 12:00:00.
Convert TIME from decimal number to standard time format (hours: minutes: seconds)
If in a cell A1 contains the time (hours) value in decimal form (10.5), then to display it in standard time format use the formula \u003d TEXT (A1 / 24; "h: mm") Result 10:30 in text format (function \u003d TEXT () returns text value). 0.5 means a fraction of an hour, i.e. half an hour, i.e. 30 minutes.
If you need the value in time format, then you can write the formula \u003d A1 / 24. The cell format containing the formula must be converted to the Time format (see the example file).
Another option: \u003d TIME (10; 30; 0) The arguments of the TIME () function can only be integers, so 0.5 hours were replaced by 30 minutes. In order not to convert fractions of hours to minutes, you can write the formula \u003d TIME (A1; REST (A1; 1) * 60; 0) The result is 10:30 AM.
If the number 145 means 145 minutes, then you can convert it to the Time format by the formula \u003d TIME (0; 145; 0). The result is 2:25 AM (in the morning), because 2 (hours) * 60 minutes + 25 minutes \u003d 145 minutes.
If the integer part of the number contains hours, and the decimal minutes, for example, 10.75, then the formula \u003d TIME (INT (A1), 100 * (A1-INT (A1)), 0) will convert this number to 11:15 time format. This approach differs from the one discussed above (10.5), when the decimal part denoted a fraction of an hour, not a minute. Those. 0.5 in the first case is 30 minutes, and in the second - 50 minutes. The main thing is not to get confused. Better - don't store the time in decimal format. Store hours and minutes in different locations, then use the TIME () function.
Converting 1800 to 18:00
4. Direct conversion to time format
In the format the date it is accepted that the number one corresponds to one day. Accordingly, the specific time is part of the day: twelve days is 0.5; six in the evening is 0.75. A number equal to 1/24 (i.e. 0.041667) corresponds to one hour. Considering that there are 24 hours in a day, 60 minutes in an hour, and 1440 minutes in a day, we write the following formula: \u003d INT (A1 / 100) / 24 + OSTAT (A1; 100) / 1440... Next, change the cell format to Time.
In Microsoft Office Excel, you can calculate data in hours, minutes, and seconds format. If you need time, use the appropriate formulas for this.
Instructions
If this is your first time encountering the task of timing, try not to resort to complex functions right away. Most calculations can be done with simple mathematical formulas: addition and subtraction. And be sure to give the cells the correct format.
To do this, select the range of cells into which the data will be entered, and right-click on the selection, or open the "Home" tab and click the "Format" button in the "Cells" toolbar. In both the first and second cases, select the "Format cells" item in the context menu.
A new dialog box will open, make sure you are on the Number tab. In the list located in the left part of the window, select the "Time" item with the left mouse button. In the right part of the window, indicate in what form should be presented time... Click on the OK button to confirm your choice.
time "class \u003d" lightbx "data-lightbox \u003d" article-image "\u003e 
Enter data in the cells in the format XX: XX (or XX: XX: XX). To calculate the time interval between the first and second value, place the cursor in the cell where the total value appears. In the formula bar, put an equal sign, click on the cell with the last value with the left mouse button, enter the minus sign, then click on the cell with the first value. Press the Enter key.
For example: the time elapsed from 11:10:15 to 12:30:00 is 1 hour, 19 minutes and 45 seconds. The total value in cell C2 is calculated by the formula: \u003d B2-A2. If you need to count the time span in several rows (A3 and B3, A4 and B4, and so on), simply drag the autocomplete marker from cell C2 to the end of your table column (C3, C4).
time "class \u003d" lightbx "data-lightbox \u003d" article-image "\u003e 
If you need to calculate how many total hours, minutes and seconds it took for something, simply add the data obtained in the way described above using the SUM function. Place the cursor in the cell with the total value, click on the fx button, in the window that appears, select the SUM function with the left mouse button and mark the range of cells that will participate in the calculation. Press the Enter key. The formula for the calculation will look like this: \u003d SUM (C2: C4).
time "class \u003d" lightbx "data-lightbox \u003d" article-image "\u003e 
When working with time in Excel, sometimes there is a problem of converting hours into minutes. It would seem a simple task, but often it turns out to be too tough for many users. And the point is all about the peculiarities of calculating time in this program. Let's see how you can convert hours to minutes in Excel in various ways.
The whole difficulty of converting hours into minutes is that Excel considers time not in the way we are used to, but in days. That is, for this program, 24 hours are equal to one. The time 12:00 is represented by the program as 0.5, because 12 hours is 0.5 of a day.
To see how this happens in an example, you need to select any cell on the sheet in time format.
And then format it to the general format. It is the number that appears in the cell that will reflect the program's perception of the entered data. Its range can range from 0 before 1 .
Therefore, the issue of converting hours into minutes should be approached precisely through the prism of this fact.
Method 1: apply the multiplication formula
The simplest way to convert hours to minutes is to multiply by a certain factor. Above, we found out that Excel perceives time in days. Therefore, to get minutes from the expression in hours, you need to multiply this expression by 60 (number of minutes in hours) and on 24 (number of hours in a day). Thus, the coefficient by which we need to multiply the value will be 60 × 24 \u003d 1440... Let's see how it will look in practice.
Method 2: using the CONVERT function
There is also another way to transform hours into minutes. To do this, you can use the special function CONVER... Please note that this option will only work when the original value is in a cell with a common format. That is, 6 hours in it should not be displayed as "6:00", but as "6", but 6 hours 30 minutes, not like "6:30", but as "6.5".
As you can see, converting hours to minutes is not as easy as it seems at first glance. This is especially problematic with time-formatted data. Fortunately, there are ways to do the transformation in this direction. One of these options is to use a coefficient, and the other to use a function.