Excel: create hourly calculator - how it works
If you create an hour calculator in Excel, you can use it to calculate the difference in hours from two times. You have to differentiate between a calculator that takes the date change into account and a simple calculator for times within a day.
Excel: Create a simple hourly calculator without changing the date
A simple hourly calculator is sufficient to calculate the difference between two times on the same day. In this example we calculate the difference between the times entered in cells A2 and B2 and output the result in C2.
- Select cells A2 to C2, right-click on them and select "Format cells".
- Select the "Numbers" tab and select the "Custom" entry under "Categories".
- Enter "hh: mm" in the "Type" field. "Hh" stands for hours, "mm" for minutes.
- Confirm with OK".
- Enter "= B2-A2" in cell C2 and confirm with Enter.
- Now enter the times to be calculated in the format "hh: mm" in cells A2 and B2. This is how you get the difference in cell C2.
- You can copy the formula and formatting to the cells below by marking the three cells and dragging the little black square in the lower right corner of the marker down.
Excel: hour calculator with date change
To use Excel to calculate the difference between two times that are not on the same day, you need a more complex calculation command. We stick to the example from above and use cells A2 to C2.
- Select cells A2 and C2, right-click on them and select "Format cells".
- Select the "Custom" category under "Numbers" and enter "hh: mm" for the type. Confirm with OK".
- Now enter the formula "= IF (B2> A2; B2-A2; B2 + 1-A2)" in cell C2 and press Enter.
- Now you can enter your times in fields A2 and B2 and get the difference in C2, even if you enter times across dates.
In our next practical tip, we will show you how to insert the date and time in Excel.