Figured out this handy and compact (meaning its not a string of =IF statements) function for turning a date into a day of the week.
=MID("SaSuM TuW ThF ", (mod(int(B2), 7)*2)+1, 2)
This just takes advantage of Google Spreadsheet’s substring-like MID() function and will result in a two-letter date code. You could change this to the whole word if you wanted, by changing the first argument of MID() to be something like “Monday Tuesday Wednesday…”, with each word padding to the same amount of characters (I think 9 would be the shortest), changing the multiplier in the second argument to that number (say, 9) and changing the last argument to the number.
In the provided code, B2 is the cell that has the date.
RSS Feed
great advice. thanks!
Thanks for that, I have another formula which looks ugly but may be more useful in that it uses the basic Google Spreadsheets function “Weekday(date,type)” which returns a number for the day of the week and which number it returns depends on the type you set (1, 2 or 3). More information is here: http://docs.google.com/support/bin/answer.py?hl=en&answer=155115 (though that page has a typo and I’ve submitted a correction)
So assuming no one sets the type away from the default, this formula should allow you to elegantly use a date in one cell to get the day of the week in the target cell. For example let’s pretend the date is in A2, and the value of 1/1/2011.
weekday(A2) will return 7, which is actually Saturday. So all you need to do is have a bunch of IF statements for each of the 7 possibilities and they must be nested so all the logic is done in one cell. Thus, this is the nested IF statement I created:
=if(weekday(A2)=1,”Sunday”,if(weekday(A2)=2,”Monday”,if(weekday(A2)=3,”Tuesday”,if(weekday(A2)=4,”Wednesday”,if(weekday(A2)=5,”Thursday”,if(weekday(A2)=6,”Friday”,if(weekday(A2)=7,”Saturday”,”FALSE”)))))))
Actually a friend of mine commented on a blogpost I did about this and reminded me that the SWITCH statement is available in Google Spreadsheets.
So better than the IF/ELSE is this:
=CHOOSE(weekday(A1), “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)
Simply use
=MID(TEXT( A2 ; “yyEEEE”); 3, 64)
Think I got a even better solution:
=text(A2;”dddd”)
The text() function is indeed better. I’m not sure that it was available when I wrote this, but either way, great job and thanks for the notes!