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"))))))
i am very much thanks to you that you give me a lot of information of this source
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
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
=TEXT(WEEKDAY(A1),"ddd")
This is easier and faster to use than the nested IF statements. Hope this helps.
Dankje voor deze handige functie, heb hem toegepast en werkt prima,
Thankx for this function, I used it en works fine
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.
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?
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.
Dear Tiposaurus,
your fourmula is right but problem is there that...if we don't put any number in the cell..it shows Sat by-default.