How to use the Excel WEEKNUM function | Exceljet

文章推薦指數: 80 %
投票人數:10人

The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting on the ... Skiptomaincontent ExcelWEEKNUMFunction Summary  TheExcelWEEKNUMfunctiontakesadateandreturnsaweeknumber(1-54)thatcorrespondstotheweekofyear.TheWEEKNUMfunctionstartscountingontheweekthatcontainsJanuary1.Bydefault,weeksbeginonSunday,butthiscanbechanged. Purpose  Gettheweeknumberforagivendate Returnvalue  Anumberbetween1and54. Syntax  =WEEKNUM(serial_num,[return_type]) Arguments  serial_num-AvalidExceldateinserialnumberformat.return_type-[optional]Thedaytheweekbegins.Defaultis1. Version  Excel2003 Usagenotes  TheWEEKNUMfunctiontakesadateandreturnsanumberbetween1and54thatcorrespondstotheweekoftheyear.Bydefault,theWEEKNUMfunctionstartscountingontheweekthatcontainsJanuary1 andincrementsweeknumberson Sunday. TheWEEKNUMfunctionacceptstwoarguments, serial_numandreturn_type.Theserial_num argumentmusthaveavalidExceldate.Thereturn_type argumentcontrolswhatdayoftheweekbeginsanewweeknumber.Return_type isoptionalanddefaultsto1,whichsetsnewweeknumberstostartonSunday.Whenreturn_typeissetto2,weeknumbersbeginonMonday.  Withareturn_typeof1-17,weeknumber1inagivenyearisassignedtotheweekthatcontainsJanuary1.Withreturn_type21,week1isthe weekcontainingthefirstThursdayoftheyear,following ISO8601.Thetablebelowsummarizes return_typeoptions. Return_type Weekbegins 1(default) Sunday 2 Monday 11 Monday 12 Tuesday 13 Wednesday 14 Thursday 15 Friday 16 Saturday 17 Sunday 21 Monday(seenoteabove) Example#1-basicusage Theformulasbelowreturntheweeknumberforthelastdayof2020andthefirstdayof2021: =WEEKNUM("31-Dec-2020")//returns53 =WEEKNUM("1-Jan-2021")//returns1 Example#2-returntype Thereturn_type argumentcontrolswhatdayofweekbeginsanewweeknumber.Bydefault,return_type is1,andnumbersincrementonSunday.Whenreturn_type isprovidedas 2,weeknumbersbeginonMonday.Forexample,January3,2021isaSundayand,WEEKNUMwillreturn2,sincenewnumbersstartonSundays: =WEEKNUM("3-Jan-2021")//returns2 However,whenreturn_type issetto2,WEEKNUMwillreturn1andstartweek2onMonday: =WEEKNUM("3-Jan-2021",2)//returns1 =WEEKNUM("4-Jan-2021",2)//returns2 Note:theexamplesaboveshowdatesastextvaluesforreadability,butworkingnativeExceldatesismorereliable.Tocreateadatefromscratchinaformula,usetheDATEfunction. WEEKNUMformulaexamples Sumbyweeknumber TheSUMIFSfunctioncansumrangesbasedonmultiplecriteria. Inthisproblem,weconfigureSUMIFStosumamountsinthenamedrangetotalbyweeknumberusingtwocriteria: color=valueincolumnG week=value... Getweeknumberfromdate TheWEEKNUMfunctiontakesadateandreturnsaweeknumber(1-54)thatcorrespondstotheweekofyear.TheWEEKNUMfunctionstartscountingwiththeweekthatcontainsJanuary1.WEEKNUM takestwoarguments: adate,... Padweeknumberswithzeros TheTEXTfunctioncanapplynumberformatsofanykind,includingcurrency,date,percentage,etc.Byapplyinganumberformatlike"00","000","0000",youcan"pad"numberswithasmanyzerosasyoulike.Zeroswill... Relatedfunctions  ExcelISOWEEKNUMFunction TheExcelISOWEEKNUMfunctiontakesadateandreturnsaweeknumber(1-54)thatfollowsISOstandards,whereweeksbeginonMondayandweeknumber1isassignedtothefirstweekinayearthatcontainsaThursday. 500Formulas| 101Functions Download100+ImportantExcelFunctions Getover100ExcelFunctionsyoushouldknowinonehandyPDF. Email HP Topics FormulaBasics Formula Examples ConditionalFormatting PivotTables ExcelTables ExcelCharts Shortcuts More... Keyfunctions IFfunction VLOOKUP function XLOOKUP function FILTER function SUMIFSfunction COUNTIFSfunction SUMPRODUCTfunction INDEXandMATCH Morefunctions... Hi-I'mDaveBruns,andIrunExceljetwithmywife,Lisa.OurgoalistohelpyouworkfasterinExcel.Wecreate shortvideos,andclearexamplesofformulas,functions,pivottables,conditionalformatting,andcharts. Readmore. Suchagreatresource!Reallyappreciateyourhelp-Carole Excelvideotraining Quick,clean,andtothepoint. Learnmore



請為這篇文章評分?