Get The Weekday Name From A Date

Spreadsheets,Tips 8 March 2011 2 Comments

Have you ever wanted to determine what kind of day it was from a date in a Google Docs spreadsheet? For example, suppose you would like to know if March 15, 1976 was a Monday or a Tuesday or a Wednesday, etc.? You can use the weekday() function, but it will return the weekday number, for example:

=weekday("03/15/1976")

sets the cell value to 2, being the number of days since Sunday where Sunday = 1. What if you want to get “Monday” from your formula? Here’s how (for the following examples, we’ll be referencing a date in cell A2; substitute the cell reference of any cell with a date in it):

  • If you want to get a three letter abbreviated weekday name, such as “Mon” for Monday, you can use the following formula:
=right(text(A2,"yyyyeee"),3)

  • If you want the full name of the weekday, as in “Monday”, you can use the following formula:
=right(text(A2,"yyyyeeee"),len(text(A2,"yyyyeeee"))-4)

Why This Works

The text() function will convert a date into text. You must also provide the function with a format string. This tells the text() function how you want the date formatted when converting it to text. For example, yyyy returns the year in four digits and yy returns the year in two digits. There are format strings for months, days, hours, minutes, etc. For example:

=text(A2, "mmm dd yyyy")

returns Mar 15 1976 to the cell. eee is the format string for a three letter abbreviated day name and eeee is the format string for the full name name. Unfortunately, there seems to be a bug in Google Docs and the following formula:

=text(A2, "eeee")

returns the incorrect value (in our case eeee27834) to the cell. To get around this, you provide a format string that contains other date components and then strip those extra date parts away in the result.

 

Tagged in , , , ,

2 Responses on “Get The Weekday Name From A Date”

  1. Joe Bourne says:

    Nice site.
    Another way to get the full Dayname is:

    =split(text(B7,”eeee|yyy”),”|”)

    Cheers!
    Joe

  2. Joe Bourne says:

    Or better still:

    =text(B9,”dddd”)

Leave a Reply