How to use the Excel WEEKNUM function | Exceljet
文章推薦指數: 80 %
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
延伸文章資訊
- 1七】日期相關函數(五)WEEKDAY、WEEKNUM - iT 邦幫忙
搞定Excel 日期時間相關計算--【七】日期相關函數(五)WEEKDAY、WEEKNUM. 你可能不知道的Office 系列第27 篇. alenchen. 9 年前‧ 16957 瀏覽.
- 2How to use the Excel WEEKNUM function | Exceljet
The Excel WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the ...
- 3Excel WEEKNUM函數的使用方法,今天是今年的第幾周?
Excel WEEKNUM函數的使用方法,今天是今年的第幾周? ... 返回特定日期的周數。 例如,包含1 月1 日的周為該年的第1 周,其編號為第1 周。 此函數可採用兩種 ...
- 4【Excel函數教學】WEEKNUM:傳回序列值對應的一年中的週數
1.按一下要輸入函數的儲存格 · 2.按一下〔插入函數〕按鈕,選取函數WEEKNUM,跳出【函數參數】對話盒,設定好參數 · 3.用公式=WEEKNUM(D3,1)-WEEKNUM($B$11,...
- 5如何使用Excel WEEKNUM函數
WEEKNUM 在Excel中,返回一年中給定日期的星期數,該星期數從1月XNUMX日開始計算星期數。默認情況下, WEEKNUM 函數計數週從星期日開始。