Expert: Stuart Resnick - 11/2/2009

Question
QUESTION: I need to accumulate minutes and then show them in an Hour/Minute format using a 24 hour clock.
Example:
Time On: Time Off: Elapsed Hours/Minutes: Accumulated hrs/mins:
0619       0622         3 minutes              3 minutes
1457       1809         12 minutes             15 minutes
0038       0138         1 hr                   1 hr 18 mins

I can calculate the Elapsed Hours/Minutes by using =(E3-D3)*1440.  But I am having difficulty with changing the Accumulated Hrs/Minutes in showing Hours %26 Minutes, I only get accumulated minutes and it rolls back to zero after a 24 hour period.


ANSWER: Since you don't specify, I'll assume Excel 2003.

Select cols A:B and Format, Cells, Number, Category: Custom, Type: hhmm, OK.

In cell A1 enter

06:19

and it will appear as 0619. Similarly, enter the rest of the times in A1:B3.

Select cols C:D and Format, Cells, Number, Category: Custom, Type: h "hr" mm "minutes", OK.

In C1 enter

=B1-A1

In D1 enter

=SUM(C$1:C1)

Then copy C1:D1 down the cols

---------- FOLLOW-UP ----------

QUESTION: I am using Excel 2007.  Your reply worked GREAT except for the Time On and Time Off for a 24 hour time.  See example:

Time On: Time Off: Elapsed Hrs/Mins: Accumulated hrs/mins:
0456am     1448pm    19H 04 min          19H 04 mins
0500am     0500am    24 hrs              43H 04 mins
0900am     0900am    24 hrs              67H 04 mins

Can you help me with accumulating time on a 24hr clock?  My time should never roll back to zero, it should always show accumulated times from start to finish over a 2 or 3 week period.

Answer
Since you now say your Time On and Time Off may be on different days, it's MUCH clearer to include the date in these entries.

So for instance, in cell A2 enter:

11/2/09 5:00 am

and in B2 enter

11/3/09 5:00 am

Format C2 as Category: Custom, Type: [h] "hrs" mm "min"

Enter in C2 the formula:

=B2-A2

and it will dispay the result as

24 hrs 00 min