Excel VBA Weekday Function - WallStreetMojo

The weekday function returns the provided date's day number in the week. For example, if you have dates 01st April to 07th April and if you want to know the day ... SkiptoprimarynavigationSkiptomaincontentSkiptoprimarysidebarSkiptofooter Home»Excel,VBA&PowerBI»VBAResources»VBAWeekdayArticlebyJeevanAYReviewedbyDheerajVaidya,CFA,FRM ExcelVBAWeekdayFunction WeekdayinVBAisadateandtimefunctionwhichisusedtoidentifytheweekdayofagivendateprovideditasaninput,thisfunctionreturnsanintegervaluefrom1to7range,thereisanoptionalargumentprovidedtothisfunctionwhichisthefirstdayoftheweekbutifwedonotprovidethefirstdayoftheweekthenthefunctionassumesSundayasthefirstdayoftheweekbydefault. Canwetelltheweekdaynumberbylookingataparticulardate?Yes,wecantellthedaynumberthatweek,dependinguponthestartingdayoftheweek.Inregularworksheetfunctions,wehaveafunctioncalledWEEKDAY inexceltotellthenumberoftheweekforaparticulardate.InVBA,too,wehavethesamefunctiontofindthesamething. Youarefreetousethisimageonyourwebsite,templatesetc,PleaseprovideuswithanattributionlinkHowtoProvideAttribution?ArticleLinktobeHyperlinkedForeg:Source:VBAWeekday(wallstreetmojo.com) WhatdoesWeekdayFunctiondo? Theweekdayfunctionreturnstheprovideddate’sdaynumberintheweek.Forexample,ifyouhavedates01stAprilto07thAprilandifyouwanttoknowthedayofthedate05thAprilifthestartingdayoftheweekisfromMonday,itisthe5thday. Tofindthis,wehavethesamefunctionas“Weekday”inaworksheetaswellasinVBA.Belowisthesyntaxofthefunction. Date:Forwhichdatewearetryingtofindtheweekday.Thisshouldbeaproperdatewiththecorrectformat. [FirstDayofWeek]:InordertodeterminetheweekdayoftheprovidedDate,weneedtomentionwhatisthefirstdayoftheweek.Bydefault,VBAconsiders“Monday”asthestartingdayoftheweek.Apartfromthis,wecansupplythebelowdaysaswell. Examples YoucandownloadthisVBAWeekDayFunctionExcelTemplatehere– VBAWeekDayFunctionExcelTemplate Example#1 Tostarttheproceedings,letmestartwithasimpleexamplefirstup.Nowwewilltrytofindtheweekdayforthedate“10-April-2019”. Step1:DefinethevariableasString Code: SubWeekday_Example1() DimkAsString EndSub Step2:Assignvaluetothevariable Assignthevaluetothevariable“k”byapplyingtheWEEKDAYfunction. Code: SubWeekday_Example1() DimkAsString k=Weekday( EndSub Step3:EnterDateinFunction Thedatewearetestinghereis“10-Apr-2019”,sopassthedateas“10-Apr-2019”. Code: SubWeekday_Example1() DimkAsString k=Weekday("10-Apr-2019" EndSub Step4:ShowValueofVariableinMsgBox Bydefault,ittakesthefirstdayoftheweekas“Monday,”soignorethispart.Closethebracket.Thenextlineshowsthevalueofvariable“k”intheVBAmessageboxVBAMessageBoxVBAMsgBoxfunctionisanoutputfunctionwhichdisplaysthegeneralizedmessageprovidedbythedeveloper.Thisstatementhasnoargumentsandthepersonalizedmessagesinthisfunctionarewrittenunderthedoublequoteswhileforthevaluesthevariablereferenceisprovided.readmore. Code: SubWeekday_Example1() DimkAsString k=Weekday("10-Apr-2019") MsgBoxk EndSub Ok,wearedone. Ifwerunthecode,wewillgettheresultas“4”becausestartingfromSunday,theprovideddate(10-Apr-2019)fallsonthe4thdayoftheweek. Note:Mysystem’sstartingdayoftheweekis“Sunday.” Similarly,ifyouchangethestartdayoftheweek,itkeepsvarying.Belowisanexamplelineforthesame. Code: k=Weekday("10-Apr-2019",vbMonday) ‘Thisreturns3 k=Weekday("10-Apr-2019",vbTuesday) ‘Thisreturns2 k=Weekday("10-Apr-2019",vbWednesday) ‘Thisreturns1 k=Weekday("10-Apr-2019",vbThursday) ‘Thisreturns7 k=Weekday("10-Apr-2019",vbFriday) ‘Thisreturns6 k=Weekday("10-Apr-2019",vbSaturday) ‘Thisreturns5 k=Weekday("10-Apr-2019",vbSunday) ‘Thisreturns4 Example#2–ArriveWhethertheDateisonWeekendorNot Assumeyouhaveadatelikethebelow,andyouwanttofindthenextweekenddate,thenwecanusetheWEEKDAYfunctiontoarriveattheresults. WeneedtouseWEEKDAYwithIFconditionandloopstoarriveattheresult.Ihavewrittenthecodeforyoutogolinebylinetogetthelogic. Code: SubWeekend_Dates() DimkAsInteger Fork=2To9 IfWeekday(Cells(k,1).Value,vbMonday)=1Then Cells(k,2).Value=Cells(k,1)+5 ElseIfWeekday(Cells(k,1).Value,vbMonday)=2Then Cells(k,2).Value=Cells(k,1)+4 ElseIfWeekday(Cells(k,1).Value,vbMonday)=3Then Cells(k,2).Value=Cells(k,1)+3 ElseIfWeekday(Cells(k,1).Value,vbMonday)=4Then Cells(k,2).Value=Cells(k,1)+2 ElseIfWeekday(Cells(k,1).Value,vbMonday)=5Then Cells(k,2).Value=Cells(k,1)+1 Else Cells(k,2).Value="ThisisactuallytheweekendDate" EndIf Nextk EndSub Thiswillarriveattheresultsbelow. LookatthecellsB6&B7.Wegottheresultas“Thisisactuallytheweekenddate”becausedates“04-May-2019”and“06-Apr-2019”areactuallyweekenddates,sononeedtoshowtheweekenddateforweekenddates.Bydefault,wegettheresultasthis. RecommendedArticles ThishasbeenaguidetoVBAWeekday.HerewelearnhowtousetheVBAweekdayfunctiontogetthelastdayoftheweekwithexamplesandadownloadableexceltemplate. BelowaresomeusefulexcelarticlesrelatedtoVBA– IFERRORFunctioninVBAVBADateDiffFunctionExcel WeeklyPlannerTemplateWhatisVBADATEVALUEFunction?VBAPasteValues VBATraining(3Courses,12+Projects) 3Courses 12Hands-onProjects 43+Hours FullLifetimeAccess CertificateofCompletion LEARNMORE>> PrimarySidebar GetFREEAccessto500+VideoTutorials SubscribetoYoutubeNow! FooterCompany Resources Courses x Let’sGetStarted EmailID* Pleaseselectthebatch Upcomingbatch* 19th/20thSeptember2020Saturday-Sunday9amISTto5pmIST 26th/27thSeptember2020Saturday-Sunday9amISTto5pmIST x DownloadVBAWeekDayFunctionExcelTemplate EnterEmailAddress x Login ForgotPassword? x FreeExcelCourse Excelfunctions,Formula,Charts,Formattingcreatingexceldashboard&others EnterEmailAddress *Pleaseprovideyourcorrectemailid.LogindetailsforthisFreecoursewillbeemailedtoyou x FreeExcelCourse Excelfunctions,Formula,Charts,Formattingcreatingexceldashboard&others EnterEmailAddress *Pleaseprovideyourcorrectemailid.LogindetailsforthisFreecoursewillbeemailedtoyou x x Cookieshelpusprovide,protectandimproveourproductsandservices.Byusingourwebsite,youagreetoouruseofcookies(PrivacyPolicy) GetFreeAccessto500+VideosinExcel,VBA&More SubscribetoWallstreetmojoYoutubeChannel 55.6Ksubscribers x