How To: Get the Weekday as Text in Excel VBA

I often want to know what day of the week a date is. Excel has a function for this, but unfortunately it only returns an integer, for example the function:
=WEEKDAY("01/02/2009")
returns 1 (which represents Sunday).

This can be a bit confusing with a list of dates, so I wrote the following quick VBA function to get the day of the week as text, e.g. Sunday, Monday, etc.

The function (possibly not the most elegant method possible) is:

' function to return a text representation of the weekday of a given date
' returns the full text, which can be shortened by left(), etc
Function dayText(d As Date) As String
If Weekday(d) = 1 Then dayText = "Sunday"
If Weekday(d) = 2 Then dayText = "Monday"
If Weekday(d) = 3 Then dayText = "Tuesday"
If Weekday(d) = 4 Then dayText = "Wednesday"
If Weekday(d) = 5 Then dayText = "Thursday"
If Weekday(d) = 6 Then dayText = "Friday"
If Weekday(d) = 7 Then dayText = "Saturday"
End Function

(Enter this via the VBA Editor – press Alt+F11, then insert -> module and paste this code in).

It can then be called by:
=DAYTEXT("01/02/2009")
which returns Sunday.

This can then be shortened to one or three letters via the left() function, for example:
=LEFT(DAYTEXT("01/02/2009"),3)
returns Sun.

If you want to avoid macros, an alternative method is to use the (slightly messy) formula consisting of nested IFs, for a spreadsheet with a date in cell A1:
=IF(WEEKDAY(A1)=1, "Sunday", IF(WEEKDAY(A1)=2,"Monday",IF(WEEKDAY(A1)=3, "Tuesday", IF(WEEKDAY(A1)=4, "Wednesday", IF(WEEKDAY(A1)=5, "Thursday", IF(WEEKDAY(A1)=6, "Friday", "Saturday"))))))

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading ... Loading ...

June 10, 2009 · Filed under VBA / Excel

8 Comments »

  1. Arup Adhikari Said,

    July 17, 2009 @ 9:03 pm

    i am very much thanks to you that you give me a lot of information of this source

  2. Ahmad Al-Musallami Said,

    July 19, 2009 @ 7:09 am

    Hi.

    I think that there is even an esier way to do it, using the cell formating (custom format).

    STEPS:
    1. Right Click the cell or press Ctrl + 1. The Format Cells window appears.

    2. From the “Number” tab, select “Custom” from “Category” list on the left.

    3. On the Type box write the following format code “ddd dd/mm/yyyy”.

    4. Click OK.

    5. Make sure that the cell if wide enough to display the number correctly, so if you see “##########” then you need to streach the column a bit wider.

    Good luck.

    Ahmad Al-Musallami
    aalmusallami@gmail.com

  3. Bernard Said,

    July 26, 2009 @ 11:25 pm

    May be a select case would be more “elegant”?
    Sub MyProcedure()
    Dim intDay%
    Dim strDay$
    intDay = Weekday(Date)
    strDay = dayText(intDay)
    End Sub
    Function dayText(intDay As Integer) As String
    Select Case intDay
    Case 1
    dayText = “Sunday”
    Case 2
    dayText = “Monday”
    Case 3
    dayText = “Tuesday”
    Case 4
    dayText = “Wednesday”
    Case 5
    dayText = “Thursday”
    Case 6
    dayText = “Friday”
    Case 7
    dayText = “Saturday”
    End Select
    End Function

  4. Atroson Said,

    July 31, 2009 @ 1:29 pm

    =TEXT(WEEKDAY(A1),”ddd”)

    This is easier and faster to use than the nested IF statements. Hope this helps.

  5. Tonnie Bleumink Said,

    August 6, 2009 @ 2:55 am

    Dankje voor deze handige functie, heb hem toegepast en werkt prima,

    Thankx for this function, I used it en works fine

  6. Tiposaurus Said,

    August 30, 2009 @ 1:10 pm

    Thanks for the comments.

    As I said in the article, the code I wrote isn’t great. At the time it was a quick hack to provide an immediate solution. A couple of comments have given much better solutions.

    #3, Bernard: Yep, a select case would certainly be more elegant. Alternatively, I suspect an improvement on my code would be to use Else If’s, rather than repeated If’s.

    #4, Atroson: Thanks, I didn’t know the TEXT() function did that! I also notice that the following seems to work in Excel 2003:
    =TEXT(A1,”ddd”) will return “Wed”, while
    =TEXT(A1,”dddd”) will return “Wednesday”
    So it doesn’t look like the weekday() function is needed.

  7. Mike Said,

    September 14, 2009 @ 7:46 pm

    Hello all, Yes! I am a quite the newbie to Excel… I am hoping that one of you will have an answer to my question:
    I am working on a production schedule in Excel. I have figured how long each job will take… now I would like to add the calculated time it will take to the start date, but also consider we only work 4 days a week (Monday- Thursday), for example: I don’t want the completion date to fall on a Saturday. Is there an easy way to achieve this?

  8. Abhijeet Said,

    October 19, 2009 @ 2:54 am

    I have a simpler formula to get weekdays
    If cell A1 has a date, i would simply use a formula =text(A1,”ddd”) and i have the mon, tue, wed,etc for the relevant date in cell A1.
    Thanks.

Leave a Comment