Google Apps Hacks

65  - SHOW ELAPSED TIMES AS DAYS AND HOURS

HaCK 23:



Using a formula, you can convert values such as “186 hours” into

the more readable “7 days 18 hours.” Inspired by the hack “Show

Total Time as Days, Hours and Minutes” from David and Raina

Hawley’s Excel Hacks, 2nd Edition, I decided to try this out in

Google documents.

To set up for this hack, create a simple time management table, as shown in Figure 3-16. The left

column contains the tasks—designing, programming, debugging, and so on—and the right column

contains the hours spent for this task. The overall time spent on the project is currently displayed in

hours, as shown in Figure 3-16, using the following formula:

=sum(B2:B12)

This adds up all numbers between cells B2 and B12. In this case, the result is 186 hours. Type “186

hours in days” without quotes into a Google web search, and the Google calculator will tell you that

this equals 7.75 days.

For a full feature list of what the Google calculator can do for you, including answering such queries as

“17 * 22 – 3,” “200 USD in EUR,” or “42 in Roman numerals,” see http://google.com/help/calculator.html.

You can get your spreadsheet to display a more readable sum, too. To do this, replace the SUM

formula you used earlier with this one (select the hours sum cell, double-click or press F2, replace

the formula with what you ...