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
延伸文章資訊
- 1How 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 ...
- 2WEEKNUM 函數
本文將說明WEEKNUM函數的公式語法及使用方式在Microsoft Excel 中。 描述. 傳回特定日期的週數。 例如,包含1 月1 日的該週是該年的第一週,因此會編號為第1 週。
- 3Excel-計算日期為第幾週
網友問到:如何計算某個日期為當年第幾週?如果不使用WEEKNUM 函數,如何做到相同結果?(參考下圖) (1) 使用WEEKNUM 函數在Excel 2010 中, ...
- 4七】日期相關函數(五)WEEKDAY、WEEKNUM - iT 邦幫忙
搞定Excel 日期時間相關計算--【七】日期相關函數(五)WEEKDAY、WEEKNUM. 你可能不知道的Office 系列第27 篇. alenchen. 9 年前‧ 16957 瀏覽.
- 5如何使用Excel WEEKNUM函數
WEEKNUM 在Excel中,返回一年中給定日期的星期數,該星期數從1月XNUMX日開始計算星期數。默認情況下, WEEKNUM 函數計數週從星期日開始。