Australian PC World - Clock time intervals

Clock time intervals

Tip
How do you calculate the difference between two times -- for example, when someone arrives and leaves work?
Let's say Chris arrives at 10pm and leaves at 6am the next morning. You enter the arrival time in cell A1, the departure time in cell B1, the formula =B1-A1 in cell C1, and then assign a numeric format that shows hours and minutes. If the duration is under 24 hours, 1-2-3 and Quattro Pro will display the proper answer, in this case 08:00. Excel, however, returns an error value.
In Excel, and in any case in which the interval is more than 24 hours, your formula must include not only the time value, but the date as well. The worksheet screen picture shows such a solution.
To create this worksheet in Excel, enter the data shown, including the formula in cell E3.

Then format cell E3 to show cumulative time: choose Format--Cells, click the Number tab, and select Custom in the Category list. Highlight [h]:mm:ss in the list of formats in the Type section, and remove the :ss from the end of the format in the "Type" edit box. Click OK when you're finished.
In 1-2-3 and Quattro Pro, you must use a slightly longer formula to convert the time interval to a label that shows both the hours and minutes. Enter the following formula in cell E3: @string(@round((((C3+D3)Ð(A3+B3)) *24),0),0)&":"&@right("0"&@string ((@mod(@round(((C3+D3)Ð(A3+B3))*24, 12),1))*60,0),2)
- Richard Scoville

Category: Spreadsheet
Issue: Feb 1997
Pages: 172