Google Apps Hacks

66 GOOGLE APPS HACKS

(modulo arithmetic), which takes two parameters, divides the rst parameter by the second, then

returns the remainder. The result will display as “7 days 18 hours,” as shown in Figure 3-17.



This approach works ne, but you can make it more readable through the use of a named range,

an easy-to-remember label assigned to a specic range of cells. In Google Spreadsheets, switch

to the Formulas tab (located toward the top of the page), click “Range names,” and select “Dene

new.” Name your range by using a label like “hours_spent” and select the range of cells (B2:B12).

(See Figure 3-18.) Save the new range and close the dialog, and you’re ready to replace your existing

formula with the following:

=INT( SUM(hours_spent) / 24 ) & " days " & MOD( SUM(hours_spent), 24 ) & " hours"

Or, to return the number of weeks, you can use the following formula, which expands the

former one:

=INT( SUM(hours_spent) / 24 / 7 ) & " weeks " & INT( MOD( SUM(hours_spent) / 24, 7 ) ) &

" days " & MOD( SUM(hours_spent), 24 ) & " hours"

Alternatively, you may also want to print an overview of the total hours spent in multiple formats,

such as seconds, minutes, hours, days, weeks, and years. You can name result cells of calculations

using the range names feature to simplify this step, as pictured in Figure 3-19, which also shows the

formulas being used (in the neighboring ...