In a previous post I went on and on about how you can calculate hours between two different dates and times, but Excel does this for you automatically. I’ll use reader comment as my example.
A-02.02.2011 10:00
B-05.02.2011 17:00
Please help me to find B-A in hh:mm
For clarity I will assume that the date format is m/d/yyyy, which is what I use here in the US.
For the A value type in 2/2/2011 10:00 and for the B value type in 5/2/2011 17:00 then in another cell subtract A from B. In the (B-A) cell enter the custom time format [h]:mm to show the result in hh:mm format.
To enter the custom time format:
- Open the Format Cells dialog box (CTRL+1)
- Select the Number tab
- Click Custom
- In the Type box enter [h]:mm
- Click OK
When you surround the h with a square bracket [] Excel will show hours as elapsed time. Without the square brackets the hours will only show values 0-23.
Should you want to include seconds, the custom time format would be [h]:mm:ss. To illustrate this I’ll change the B value to 5/2/2011 17:15:44 and the resulting formula will show 2143:15:44.
