format for time - entering, lapsed, sum

**jostro** · 06-10-2014, 05:19 PM

I'm trying to help my husband with a project to track SUMMER reading times for kids but am stuck. He has Excel 2007.
Scenario:
Boy 1 (Name col A) checks in to read at 9:20 (Col C). Checks out at 9:50 (Col D). Lapsed time 0:30 (Col E) Ideally in column B would like a sum of all the lapsed times for each day of the month. Check in and out are all A.M. Currently have those columns formatted for time (13:30). Have tried several formats for lapsed time column E: D2-C2, h:mm, text. Most worked fine but unable to get a sum for column B - this would be adding up columns E,H,K,N...

What am I missing? Suggestions?

From other posts it looks like ":" has to be entered to get correct date, unable to enter just the numbers. Is that true?

Thanks for any solutions.

**Martin Chamberlin** · 06-10-2014, 05:40 PM

Is this not just a summation of the lapsed time cells? SUM(E2,H2,K2,N2,Q2,T2) and, all the other lapsed time cells in the range

**jostro** · 06-10-2014, 06:00 PM

Yes, but sum is not generating the total time. My guess is because of format for column E. What format is best for figuring that lapsed time?
Or the format for the sum. What format should I be using there?

**Tony Valko** · 06-10-2014, 06:37 PM

Try formatting the sum cell as [h]:mm.

The brackets [ ] keep the time from rolling over into days at intervals of 24 hrs.

For example:

A1 = 9:00
A2 = 8:00
A3 = 8:00

=SUM(A1:A3)

If you use the format of h:mm the result will be 1:00 which is clearly not correct.

Use [h]:mm and you'll get the correct result of 25:00.

**jostro** · 06-13-2014, 11:22 PM

Thanks so much. Went to work with husband and got the spread sheet working correctly. Now just have to get him to use it.

**Tony Valko** · 06-14-2014, 08:56 AM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.