Google Apps Hacks

69  - SHOW THE DIFFERENCE BETWEEN TWO DATES

the day names as shown in Figure 3-20. And if you prefer the weekday to be shown in abbreviated

form, like “Sun,” “Mon,” and “Tue,” you can wrap this whole formula in the LEFT function, which takes

two parameters (a text string and the number of letters to print):

=LEFT( CHOOSE( WEEKDAY(B2), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",

"Saturday" ), 3 )

Similarly, if you want to show the name of a month, you can use the MONTH function:

=CHOOSE( MONTH(B2), "January", "February", "March", "April", "May", "June", "July", "August",

"October", "November", "December" )

Although these examples and the Google Spreadsheets formula documentation show all function names

written in uppercase, you can also write them in lowercase or mixed case.

HaCK 25:



Solve the question “how long ago?” with a straightforward formula.

Suppose you want to nd out the number of days since a given date, such as when a user registered.

Given a spreadsheet listing the dates, as shown in Figure 3-21 (see  for the weekday

calculation), this is a straightforward subtraction between two dates; the current date, which you can

access via the NOW function, minus the old date (B2 for the rst data cell):

=INT( NOW() - B2 )

The INT function cuts off the fraction of the number by rounding it to its nearest ...