Total Pageviews

Sunday, October 09, 2011

How To Obtain The Day of Week Given a Date in Google Spreadsheets

Google Spreadsheets has a nifty function named CHOOSE which exhibits behavior like the switch statement programmers all know and love. Basically, it allows one to select an action/value based on some input. This is GREAT!

I needed to fill cells with the day of the week given a date. There isn't any built-in function that performs this task in Google Spreadsheets right now. But, we can solve this very easily using the CHOOSE function.
=CHOOSE( weekday(H1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
This formula is very simple:

  1. The contents of cell H1 holds the date
  2. The function weekday converts that date to a number from 1 through 7 representing the day of the week (Sunday=1)
  3. This value, in turn, is then used by CHOOSE to select which value in the subsequent list to use in the current cell
  4. Voila
E... to the Z.

In this case, we are converting dates to days of the week in the format I wanted. Now we can start getting all fancy. Imagine, instead of days of the week, the list of possible values were different formulas to compute some result. The formula to use depends on the value inserted in the first input to CHOOSE.  However, remember that nothing is stopping even this first input from being yet another formula computing a specific result based on the data in particular cells. 

With this function, the possibilities are endless!

Reference: See the second comment on this blogpost