Total Pageviews

Sunday, October 09, 2011

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

UPDATE 2017-10-12:
As noted in the comments, there is an even much easier way to do this now!

=TEXT(A1,"ddd")

At some point Google updated the text function to take formatting parameters which makes this a cinch.

The old post from 2011-10-09 follows below and is a great introduction to the CHOOSE function. But don't use it for getting the day of week anymore!



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

4 comments:

Anonymous said...

wow thank you for this. This is exactly what I was looking for.

Moi said...

Thank you!

Skin Care Product said...

A very effective and cheap solution to get rid of annoying skin lesions on your body like moles, and skin tags by Skintology MD. It is a safe way to remove the skin tags and moles you have. This cream has been designed to cater to all skin types. Be it oily or normal, dry or sensitive, it works on all skin types. Kindly Visit on http://www.choosetolose.net/skintology-md-cream-how-to-remove-skin-tags-moles/

Weight Loss Diet said...

Rapid Slim is a doctor-formulated diet pill, designed for the fast-paced lives of women. The pill was created to be discreet and easy to take. It’s a liquid gel caplet that uses quick Fix technology. This means the ingredients have been reduced to a liquid state within the capsule. Kindly Visit on http://www.rushyourtrial.com/coupon/rapid-slim-keto-pills-advanced-weight-loss-supplement/