Office challenge: Come up with a formula that subtotals elapsed time by the day

In this week's Office challenge, learn the solution to last week's Word challenge and test your Date/Time arithmetic skills in this week's Excel challenge.

This week's challenge has two parts. Feel free to respond to either question. The worksheet below tracks elapsed time. The first problem is evident—the formula in column D (=$C2-$B2) can't handle a time period that extends into the next day (past midnight). There's a simple one-formula solution to this problem, what is it? (By the way, the format for D2:E8 is the custom format h:mm.) Now, here's part two of this week's challenge. Come up with a formula that subtotals each day in column E. In other words, in cell E2 or E4, the formula would return 2:50, for two hours and 50 minutes total on June 2, 2010. There are no restrictions, but the less work, the better.

Last week we asked…

How do you repeat an action in Word? Jbentley was the first to respond with Redo, [Ctrl]+Y. JBenton mentioned [F4], which was what I use most often. JBenton also mentioned [Alt]+Enter. A couple of you suggested writing a macro, which is a good idea if one of the shortcut keystrokes isn't adequate. A few of you mentioned Format Painter—always a good choice when just formatting is repeated. Don't forget, if you double-click Format Painter, you lock it in. Then, you can click as many spots as you like. Double-click it again to unlock it. Thanks to everyone for a great challenge!


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks