Use Excel To Keep Track Of Time

Q. One of my duties is to keep a log of employees’ hours worked. I know Excel has a time-function application, so I figured it would not only track the hours but even give me end-of-week totals. But try as I might, I can’t get Excel to tote up each day’s hours. Rather than providing an end-of-the-week sum, the total at the bottom of the column is displayed as a time—such as 4:03 p.m. I assume I’m doing something wrong but I can’t figure out my error. Can you help?

A. I know what you’re doing wrong. It’s a subtle misstep that has to do with the way you formatted the cells that contain the hours. For Excel to add the hours you must format in a special way.

Here’s how: Highlight the cells that will contain the hours and right-click to open the format menu. Then click on Format Cells and Number . Now scroll down to Time , then to Custom and select [h]:mm:ss . The h stands for hours, mm for minutes and ss for seconds.

If an employee worked 1 hour and 50 minutes, enter it this way: 1:50:00 . In the example at left, I use the conventional Sum formula to add the hours: =sum(B2:B4).

Also, you can save yourself time by accessing Excel’s time-sheet templates, which have all the necessary formulas built in. To access them, engage Help (press F1) or type templates in the Type a question for help box.