Excel stores dates and times as serial numbers, which are the number of days since January 1, 1900, with fractional parts representing the hours, minutes, and seconds. For example, the date 6/2/2024 is stored as 44517, and the time 8:38:38 AM is stored as 0.359699074. To calculate the number of hours between two dates, we need to subtract the serial numbers of the dates and multiply the result by 24, which is the number of hours in a day. For example, to calculate the number of hours between 6/2/2024 8:38:38 AM and 6/3/2024 10:45:12 AM, we can use the formula:
=(6/3/2024 10:45:12 AM – 6/2/2024 8:38:38 AM) * 24
This will give us 26.10972222, which is the number of hours between the two dates.
Procedures
To calculate the number of hours between two dates in Excel, we can follow these steps:
- Enter the start date and time in one cell, and the end date and time in another cell. For example, enter 6/2/2024 8:38:38 AM in cell A1, and 6/3/2024 10:45:12 AM in cell B1.
- In another cell, enter the formula =(B1-A1)*24, where B1 and A1 are the references of the cells that contain the dates. For example, enter =(B1-A1)*24 in cell C1.
- Press Enter to get the result. Excel will display the number of hours between the two dates in the cell. For example, cell C1 will show 26.10972222.
- Optionally, you can format the cell to show the result with a specific number of decimal places or as a duration. To do this, right-click on the cell, select Format Cells, and choose the desired format from the Number tab. For example, you can choose Number and set the Decimal places to 2, or choose Custom and enter [h]:mm:ss to show the result as 26:06:35.
Explanation
The formula =(B1-A1)*24 works by subtracting the serial numbers of the end date and the start date, and multiplying the difference by 24. This is because Excel stores dates and times as fractions of a day, so multiplying by 24 converts the fraction to hours. For example, if the end date is 6/3/2024 10:45:12 AM, Excel stores it as 44518.448194444, which is the number of days since 1/1/1900 plus the fraction of the day that represents 10:45:12 AM. Similarly, if the start date is 6/2/2024 8:38:38 AM, Excel stores it as 44517.359699074, which is the number of days since 1/1/1900 plus the fraction of the day that represents 8:38:38 AM. When we subtract the start date from the end date, we get 1.08849537, which is the fraction of a day that represents the difference between the two dates. When we multiply this by 24, we get 26.10972222, which is the number of hours that represents the difference between the two dates.
Example
To illustrate how to calculate the number of hours between two dates in Excel, let us consider a scenario where we want to find out how many hours a project took to complete. Suppose the project started on 5/28/2024 9:15:24 AM and ended on 6/1/2024 4:32:18 PM. We can use the following table to enter the data and the formula in Excel:
| Start Date and Time | End Date and Time | Hours |
|---|---|---|
| 5/28/2024 9:15:24 AM | 6/1/2024 4:32:18 PM | =(B2-A2)*24 |
The result will be 103.2811111, which means the project took 103.28 hours to complete. We can format the cell C2 to show the result with two decimal places or as a duration. Here is how the table will look like after formatting:
| Start Date and Time | End Date and Time | Hours |
|---|---|---|
| 5/28/2024 9:15:24 AM | 6/1/2024 4:32:18 PM | 103.28 |
| 5/28/2024 9:15:24 AM | 6/1/2024 4:32:18 PM | 103:16:52 |
Other Approaches
There are other ways to calculate the number of hours between two dates in Excel, such as using the DATEDIF function or the NETWORKDAYS function. However, these functions have some limitations and assumptions that may not suit every situation. For example, the DATEDIF function does not account for the time part of the dates, and the NETWORKDAYS function assumes a standard workweek of Monday to Friday and excludes holidays. Therefore, the formula =(B1-A1)*24 is the most flexible and accurate way to calculate the number of hours between two dates in Excel.
