How to get the day of the week as text in Excel 2010

Excel WEEKDAY function will return the day of the week as a number. See details here:

How-to-get-the-day-of-the-week-in-excel-2010

To return the day of the week as text (name of the day), we combine the WEEKDAY function with the TEXT function as follows:

TEXT(WEEKDAY(A1;2);”ddd”) will give the abbreviated name of the day : Sun , Mon, Tue … etc.

TEXT(WEEKDAY(A1;2);”dddd”) will give the full name of the day : Sunday , Monday … etc.

 

Examples:

 

Cell A1 have the date: 25/11/2012, which is Sunday.

 

1. Cell C1 have the formula : =TEXT(WEEKDAY(A1;1);”ddd”). The value in C1 will be “Sun”

2. Cell C1 have the formula : =TEXT(WEEKDAY(A1;17);”ddd”). The value in C1 will be “Sun”

3. Cell C1 have the formula : =TEXT(WEEKDAY(A1;2);”ddd”). The value in C1 will be “Sat”

Why the returned day of the week is “Sat” instead of “Sun” in example No. 3 above? This is because the TEXT function will always assume 1 as Sunday, 2 as Monday and so on. This is only compatible with WEEKDAY return type of 1 and 17. So you have to be careful if you are using a return type other than these two. You have to use some arithmetic to get the correct name of the day in this case.

 

Comments

  1. Mahesh Kulkarni says:

    There is no need to use weekday function
    simply Text(A1,”ddd”) or Text(A1,”dddd”) will work .
    It works in Excel 2003 , so it should work in advanced versions of Excel as well.

  2. You are absolutely right. What a pleasure to learn from your readers.