MS Excel: How to use the WEEKDAY Function (WS, VBA)

The WEEKDAY function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) and a VBA ... Advertisements MSExcel:HowtousetheWEEKDAYFunction(WS,VBA) ThisExceltutorialexplainshowtousetheExcelWEEKDAYfunctionwithsyntaxandexamples. Description TheMicrosoftExcelWEEKDAYfunctionreturnsanumberrepresentingthedayoftheweek,givenadatevalue. TheWEEKDAYfunctionisabuilt-infunctioninExcelthatiscategorizedasaDate/TimeFunction.Itcanbeusedasaworksheetfunction(WS)andaVBAfunction(VBA)inExcel.Asaworksheetfunction,theWEEKDAYfunctioncanbeenteredaspartofaformulainacellofaworksheet.AsaVBAfunction,youcanusethisfunctioninmacrocodethatisenteredthroughtheMicrosoftVisualBasicEditor. Syntax ThesyntaxfortheWEEKDAYfunctioninMicrosoftExcelis: WEEKDAY(serial_number,[return_value]) ParametersorArguments serial_number Adateexpressedasaserialnumberoradateinquotationmarks. return_value Optional.Itdeterminesthedaytouseasthefirstdayoftheweekinthecalculations. Warning:Thereturn_valueparameteracceptsdifferentvaluesdependingonwhetheryouareusingtheWEEKDAYfunctionasaworksheetfunctionoraVBAfunction. WorksheetFunction Thereturn_valueparameter,whenusedasaworksheetfunction,canbeanyofthefollowingvalues: Value Explanation Version 1 Returnsanumberfrom1(Sunday)to7(Saturday). Thisisthedefaultifparameterisomitted.   2 Returnsanumberfrom1(Monday)to7(Sunday).   3 Returnsanumberfrom0(Monday)to6(Sunday).   11 Returnsanumberfrom1(Monday)to7(Sunday). *IntroducedinExcel2010 12 Returnsanumberfrom1(Tuesday)to7(Monday). *IntroducedinExcel2010 13 Returnsanumberfrom1(Wednesday)to7(Tuesday). *IntroducedinExcel2010 14 Returnsanumberfrom1(Thursday)to7(Wednesday). *IntroducedinExcel2010 15 Returnsanumberfrom1(Friday)to7(Thursday). *IntroducedinExcel2010 16 Returnsanumberfrom1(Saturday)to7(Friday). *IntroducedinExcel2010 17 Returnsanumberfrom1(Sunday)to7(Saturday). *IntroducedinExcel2010 Note:StartinginExcel2010,Microsofthasintroducednewvaluesforthereturn_valueparameterwhenusedasaworksheetfunction.Youcannowuse11through17asvalidparameters.Thisallowsyoutochangethefirstdayoftheweektoanyday(MondaythroughSunday). VBAFunction Thereturn_valueparameter,whenusedasaVBAfunction,canbeanyofthefollowingvalues: Value Explanation vbUseSystemDayOfWeek Returnsanumberfrom1to7andusesyoursystemsettingstodeterminethefirstdayoftheweek vbMonday Returnsanumberfrom1(Monday)to7(Sunday). vbTuesday Returnsanumberfrom1(Tuesday)to7(Monday). vbWednesday Returnsanumberfrom1(Wednesday)to7(Tuesday). vbThursday Returnsanumberfrom1(Thursday)to7(Wednesday). vbFriday Returnsanumberfrom1(Friday)to7(Thursday). vbSaturday Returnsanumberfrom1(Saturday)to7(Friday). vbSunday Returnsanumberfrom1(Sunday)to7(Saturday). Returns TheWEEKDAYfunctionreturnsanumericvalue. AppliesTo ExcelforOffice365,Excel2019,Excel2016,Excel2013,Excel2011forMac,Excel2010,Excel2007,Excel2003,ExcelXP,Excel2000 TypeofFunction Worksheetfunction(WS) VBAfunction(VBA) Example(asWorksheetFunction) Let'slookatsomeExcelWEEKDAYfunctionexamplesandexplorehowtousetheWEEKDAYfunctionasaworksheetfunctioninMicrosoftExcel: BasedontheExcelspreadsheetabove,thefollowingWEEKDAYexampleswouldreturn: =WEEKDAY(A1) Result:1 =WEEKDAY(A1,1) Result:1 =WEEKDAY(A1,2) Result:7 =WEEKDAY(A2) Result:5 =WEEKDAY(A3) Result:6 =WEEKDAY(38157) Result:7 =WEEKDAY("Apr21,2015") Result:3 **StartinginExcel2010,youcanuse11through17asthesecondparameterandchangethefirstdayoftheweek(inthecalculations) =WEEKDAY(DATE(2015,3,15),11) Result:7(firstdayoftheweekisMonday) =WEEKDAY(DATE(2015,3,15),12) Result:6(firstdayoftheweekisTuesday) =WEEKDAY(DATE(2015,3,15),13) Result:5(firstdayoftheweekisWednesday) =WEEKDAY(DATE(2015,3,15),14) Result:4(firstdayoftheweekisThursday) =WEEKDAY(DATE(2015,3,15),15) Result:3(firstdayoftheweekisFriday) =WEEKDAY(DATE(2015,3,15),16) Result:2(firstdayoftheweekisSaturday) =WEEKDAY(DATE(2015,3,15),17) Result:1(firstdayoftheweekisSunday) Example(asVBAFunction) TheWEEKDAYfunctioncanalsobeusedinVBAcodeinMicrosoftExcel. Let'slookatsomeExcelWEEKDAYfunctionexamplesandexplorehowtousetheWEEKDAYfunctioninExcelVBAcode: DimLWeekdayAsInteger LWeekday=Weekday("12/31/2001",vbSunday) Inthisexample,thevariablecalledLWeekdaywouldnowcontainthevalueof2. FrequentlyAskedQuestions Question:IsthereaLIKEfunctioninExcelsimilartotheoneinAccess?I'mtryingtowriteaformulaequivalenttothefollowing: =if(D14like"*Saturday*",Now()+2,Now()+1) WherecellD14isadatevalueformattedasSaturday,August27,2005. Answer:SinceyourvalueincellD14isadatevalue,youcanusetheWEEKDAYfunctiontodeterminewhichdayoftheweekitis.Inthiscase,youarelookingforaSaturday.TheWEEKDAYfunctionwillreturnavalueof7whenthedatefallsonaSaturday. Tryusingthefollowingformula: =if(Weekday(D14)=7,Now()+2,Now()+1) Shareon: Advertisements