VBA Convert date to week number - Stack Overflow

To make the Week Number with Monday as a first day, use the following: WorksheetFunction.WeekNum(now, vbMonday). Home Public Questions Tags Users Collectives ExploreCollectives FindaJob Jobs Companies Teams StackOverflowforTeams –Collaborateandshareknowledgewithaprivategroup. CreateafreeTeam WhatisTeams? Teams CreatefreeTeam CollectivesonStackOverflow Findcentralized,trustedcontentandcollaboratearoundthetechnologiesyouusemost. Learnmore Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. Learnmore VBAConvertdatetoweeknumber AskQuestion Asked 4years,1monthago Active 7monthsago Viewed 66ktimes 8 1 InVBAIwanttoconvertadateas03/11/2017(DD/MM/YYYY)intotheweeknumberforthatdate. UntilnowIhavethefollowingcode: 'getingthedateoutofthestring HeadlineTemp=Mid(VRHeadline,InStr(VRHeadline,"[")+1,10) 'switch"."to"/" HeadlineTemp=Replace(HeadlineTemp,".","/") 'converttoadate FristVRFirstKW=CDate(HeadlineTemp) Now,Ineedafunctiontoconvertthatdateintotheweeknumberoftheyear.FirstweekdayisMonday. vbaexceldate Share Improvethisquestion Follow editedOct27'17at11:33 Vityata 40.8k77goldbadges4646silverbadges8181bronzebadges askedOct27'17at11:28 USER7423USER7423 16311goldbadge11silverbadge66bronzebadges 3 Excelhasabuilt-informulaforthis(WEEKNUM). – ImaginaryHuman072889 Oct27'17at11:42 @ImmaginaryHumanYes,IknowthatinExcelisthatbuiltinformula,butapparently,inVBAisnot...soIneedtocreateone. – USER7423 Oct27'17at11:44 2 Trythis:MsgBox"Wearecurrentlyinweek"&Application.WorksheetFunction.WeekNum(DateSerial(2017,10,27)) – Ralph Oct27'17at11:49 Addacomment  |  7Answers 7 Active Oldest Votes 16 TomaketheWeekNumberwithMondayasafirstday,usethefollowing: WorksheetFunction.WeekNum(now,vbMonday) Share Improvethisanswer Follow editedMar7'19at18:49 answeredOct27'17at11:51 VityataVityata 40.8k77goldbadges4646silverbadges8181bronzebadges 1 1 ThanksVityata,andalsothanksforhelpingmewithediting. – USER7423 Oct27'17at11:54 Addacomment  |  8 UsingVBA,toconvertadateintoanisoWeeknumber,youmerelyneedtheDatePartfunction(whereDTisthedateofinterest): isoWeekNumber=DatePart("ww",DT,vbMonday,vbFirstFourDays) IfyouwanttouseotherdefinitionsthanthatspecifiedinISO8601,investigatesomeoftheotheroptionsforFirstDayOfWeekandFirstWeekOfYear NOTE Aspointedoutby@Mike85,thereisabuginDatePart(andalsointheFormat)functionwhereinMondaymaybeerroneouslygivenaweeknumberof53whenitshouldbe1. Thereareavarietyofworkarounds. InExcel2013+(ExcelforMac2011+)youcanusefortheISOWeeknumber: isoWeekNumber=WorksheetFunction.isoWeekNum(dt) Forearlierversions,youcantesttheMondayandadjustitifnecessary,oryoucanwriteaseparateroutine. Share Improvethisanswer Follow editedFeb14'20at21:19 answeredOct27'17at17:27 RonRosenfeldRonRosenfeld 44k77goldbadges2424silverbadges5252bronzebadges 3 Thanksforthehint.DefinetlyIwilltrytodothis. – USER7423 Oct30'17at7:18 Thisfunctionhavebugsforleapyear.Needtogetaround.Seemyanswer. – mike85 Feb14'20at3:43 @mike85Thanksforpointingthatout.Iwasnotawareofthatbugin2017whenIwrotethisanswer. – RonRosenfeld Feb14'20at21:19 Addacomment  |  4 Becarefullwhenitcomestoweeknumbersastherearedifferentdefinitionsaround.TheExceldefinitiondiffersfromtheISOdefinition.TogettheISOweeknumberuse(copiedFromhttp://www.rondebruin.nl/win/s8/win001.htm) PublicFunctionIsoWeekNumber(dAsDate)AsInteger Dimd2AsLong d2=DateSerial(Year(d-Weekday(d-1)+4),1,3) IsoWeekNumber=Int((d-d2+Weekday(d2)+5)/7) EndFunction Share Improvethisanswer Follow answeredOct27'17at12:03 FunThomasFunThomas 13.5k11goldbadge1414silverbadges3232bronzebadges 1 Thanks@FunThomas,thishelp. – USER7423 Oct27'17at12:11 Addacomment  |  0 WeekdayName(number,[abbreviate],[firstdayofweek]) WeekdayName(2) Result:'Monday' WeekdayName(2,TRUE) Result:'Mon' WeekdayName(2,TRUE,vbMonday) Result:'Mon' Share Improvethisanswer Follow editedOct27'17at12:25 braX 10.5k55goldbadges1717silverbadges3232bronzebadges answeredOct27'17at11:40 manimani 344bronzebadges 1 Niceinformationbutdoesn'tactuallyaddressthequestionwhichasksforaweeknumberfromadate(andisactuallyansweredbyRalph'scomment) – SlowLearner Nov6'17at3:19 Addacomment  |  0 So,thisismyfinalandworkingperfectlyversion PublicFunctionIsoWeekNumber(dAsDate)AsString DimkwtempAsString kwtemp=DatePart("ww",d,vbMonday,vbFirstFourDays) IfLen(kwtemp)=1Thenkwtemp="0"&kwtemp IsoWeekNumber=kwtemp EndFunction IfApplication.International(xlMDY)=TrueThen HeadlineTemp=Mid(VRHeadline,InStr(VRHeadline,"[")+1,10) HeadlineTemp=Replace(HeadlineTemp,".","/") HeadlineTemp=Mid(HeadlineTemp,4,3)&Left(HeadlineTemp,2)&Right(HeadlineTemp,5) VRFirstKW=CDate(HeadlineTemp) HeadlineTempEndKW=Mid(VRHeadline,InStr(VRHeadline,"]")-10,10) HeadlineTempEndKW=Replace(HeadlineTempEndKW,".","/") HeadlineTempEndKW=Mid(HeadlineTempEndKW,4,3)&Left(HeadlineTempEndKW,2)&Right(HeadlineTempEndKW,5) VREndKW=CDate(HeadlineTempEndKW) VRKW="KW"&IsoWeekNumber(VRFirstKW)&"-"&IsoWeekNumber(VREndKW)&"/"&Year(VREndKW) Else'don'tswitchpositionofthemonthwithdays HeadlineTemp=Mid(VRHeadline,InStr(VRHeadline,"[")+1,10) HeadlineTemp=Replace(HeadlineTemp,".","/") VRFirstKW=CDate(HeadlineTemp) HeadlineTempEndKW=Mid(VRHeadline,InStr(VRHeadline,"]")-10,10) HeadlineTempEndKW=Replace(HeadlineTempEndKW,".","/") VREndKW=CDate(HeadlineTempEndKW) VRKW="KW"&IsoWeekNumber(VRFirstKW)&"-"&IsoWeekNumber(VREndKW)&"/"&Year(VREndKW) Share Improvethisanswer Follow answeredNov10'17at15:46 USER7423USER7423 16311goldbadge11silverbadge66bronzebadges Addacomment  |  0 CalculateISOyearusingdatepartwithbugsworkaround: 'Test2007-12-31shouldreturnW01Y2008 myDate="2007-12-31" ISOWeek=DatePart("ww",myDate,vbMonday,vbFirstFourDays) Week1=DatePart("ww",myDate,vbMonday,vbFirstFourDays) Week2=DatePart("ww",DateAdd("d",7,myDate),vbMonday,vbFirstFourDays) ISOYear=DatePart("yyyy",myDate,vbMonday,vbFirstFourDays) Year1=DatePart("yyyy",myDate,vbMonday,vbFirstFourDays) Year2=DatePart("yyyy",DateAdd("d",7,myDate),vbMonday,vbFirstFourDays) IfISOWeek=53AndDatePart("ww",DateAdd("d",7,myDate),vbMonday,vbFirstFourDays)=2Then ISOWeek=1 EndIf ifISOWeek=1AndDatePart("yyyy",DateAdd("d",7,myDate),vbMonday,vbFirstFourDays)>ISOYearThen ISOYear=ISOYear+1 EndIf MsgBox("W"&ISOWeek&"Y"&ISOYear) 'ResultinW01Y2008 Share Improvethisanswer Follow answeredFeb14'20at3:49 mike85mike85 56133silverbadges88bronzebadges Addacomment  |  0 Cw=DatePart("ww",d,vbWednesday,vbFirstFullWeek) ww->forCalendarWeek d->datevariable vbWednesday->Startingdayofweek vbFirstFullWeek->yearwillbestartedfromfirst7 Share Improvethisanswer Follow answeredMay7at9:18 MayurRajMayurRaj 4155bronzebadges Addacomment  |  YourAnswer ThanksforcontributingananswertoStackOverflow!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy Nottheansweryou'relookingfor?Browseotherquestionstaggedvbaexceldateoraskyourownquestion. TheOverflowBlog Smashingbugstosetaworldrecord:AWSBugBust Podcast399:ZerotoMVPwithoutprovisioningadatabase FeaturedonMeta Reducingtheweightofourfooter NewresponsiveActivitypage Communityinputneeded:Therulesforcollectivesarticles A/BtestingontheAskpage Linked 0 Needtoopenafilebasedonweeknumberanddate 0 DatetoWeeknuminAccess/Excel/VBA 1 ProblemwithWeekNumbersbetweendatesonNewYear 0 vba-getweeknumber -2 Howtocopypasteinadeterminatecell,changingthevariableeveryweek(notinthecode) 0 DynamicFormulatoFactorintheWeekend Related 1986 HowtoreturnonlytheDatefromaSQLServerDateTimedatatype 1423 AdddaystoJavaScriptDate 1464 WherecanIfinddocumentationonformattingadateinJavaScript? 1799 Detectingan"invaliddate"DateinstanceinJavaScript 2699 HowdoIgetthecurrentdateinJavaScript? 1296 Calculatedifferencebetweentwodates(numberofdays)? 2893 HowtoformataJavaScriptdate 963 Javastringtodateconversion 344 ConvertUTCEpochtolocaldate 770 HowdoIgetthedayofweekgivenadate? HotNetworkQuestions Whyaregraphsrepresentedasadjacencylistsinsteadofadjacencysets? Whydidn'tthemilitarygiveT-rexclonesabiggermorepowerfularms? Myadvisorwantsmetoquittheprogram.WhatshouldIdo? Isitacrimetohavechildoutsideofwedlock? Whatistheratiooffattoflourinshortcrustpastry? Photoofspritesinacleardarksky,isthispossible? Determineifawordisapalindrome HowdoIleaveacompanyongoodtermsifmyprojectmanagerviewsleavingthecompanyasaformofbetrayal? cunnilingusvscunnilinctus Whatisthecovariancematrixofthenormalorderstatistics? WhyarescientistssayingthattheOmicronCOVID-19variantisareasontogetabooster? Whatisthepurposeofacarriersignalincommunicationtechnology? Howtogetarangedbonusactionattackbefore/withoutattackingorusingacombatmanoeuvre? EventDispatchingSystem DoIholdthecopyrightontestanswers? (Jehovah'sWitnesses)Whatreason(s)istheretoactuallyloveGod? HowcanIavoidoverdraftfeeswhilemovingallofmyfundstoanewbank? What'sthegrammarusedin"justwhatquarterhedidnotnowremember"fromthebook1984? Biologicalsiegeweapons:Part1-Arelivingthingscapableofbreakingdownthegatesofamedievalgatehouse? Whatismeantbyfilterorderofapassivefilter? BatchesunderBulkAPIjobssalesforce Isthereanysortofnaturalmechanismthatwouldexplainwhymygiantsaretallerorshorterdependingonhowmanyhornstheyhave? Isitcorrectandnaturaltosay"I'llmeetyouat$100"meaningI'llaccept$100forsomething? RemovingDuplicatesfromaCSVbasedonspecifiedcolumns morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. lang-vb Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings