Function to return the day of the week in text (Name of the day)
Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return the name of the day itself i.e. Monday, Tuesday…etc.The following code is an attempt to handle this case.
Function WeekdayText(dDate, iRtype)
‘ get the weekday no. from excel bilt-in Weekday function
iwday = WorksheetFunction.Weekday(dDate)
If iRtype = 2 Then
‘ Return the full name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddddd”)
Else
‘ Return the short name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddd”)
End If
End Function
The function accepts two arguments: Date and return type. If the return type is 1, the function will return an abbreviated name of the day i.e. Mon, Tue …. etc. If the return type is 2, the function will return the full name of the day i.e. Monday, Tuesday …. etc.
Use this code to create a user defined function and then you can use it like any other built-in function.
If you are not familiar with visual basic then you can achieve the same result by encapsulating the Weekday function within the Text function, in a cell, as follows:
=TEXT(WEEKDAY(date),”ddd”)
Related Links:
How to create a user-defined function
Applies to: Excel 2003
