7 Ways To Get The Weekday Name From A Date In Excel

Format a Date as the Weekday Name · Select the dates which we want to convert into weekday names. · Go to the Home tab and click on the small launch icon in the ... 7WaysToGetTheWeekdayNameFromADateInExcelWhatdayoftheweekis2019-11-14?IsitaMonday,Tuesday,Wednesday,etc…It’sprettycommontowanttoknowwhatdayoftheweekagivendatefallsonandunlessyou’vegotsomesortofgiftforknowingthat,you’regoingtoneedawaytofigureitout.DownloadExampleFilesInExcel,therearemanydifferentwaystodeterminethis. Inthispost,we’regoingtoexplore7waystoachievethistask.TableofContents FormataDateastheWeekdayNameGettheWeekdayNamewiththeTEXTFunctionGettheWeekdayNumberwiththeWEEKDAYFunctionGettheWEEKDAYNameUsingPowerQueryGettheWEEKDAYNameinaPivotTablewiththeWEEKDAYDAXFunctionGettheWeekdayNameinaPivotTablewiththeFORMATDAXFunctionGettheWeekdayNamewithaPowerPivotCalculatedColumnConclusionsFormataDateastheWeekdayNameThefirstoptionwe’regoingtolookatinvolvesformattingourdatecells.DatesinExcelarereallyjustserialnumbersstartingat1forthedate1900-01-01.Formattingiswhatmakesthedatelooklikeadate.Therearemanywaystoformattheseserialnumberstodisplaythedateinvariousformatslikeyyyy-mm-dd,dd/mm/yyyy,dd-mmm-yyetc…Oneofthepossibleformattingoptionsfortheseserialnumbersistodisplaytheweekdaynamewithacustomddddordddformat.WecanformatourdatesfromtheFormatCellsdialogbox.Selectthedateswhichwewanttoconvertintoweekdaynames.GototheHometabandclickonthesmalllaunchiconinthelowerrightcorneroftheNumbersection.ThiswillopenuptheFormatCellsdialogbox.WecanalsoopenuptheFormatCellsdialogafewotherways.ThekeyboardshortcutCtrl+1.Rightclickontheselectedcells➜chooseFormatCellsfromthemenu.GototheNumbertabintheFormatCellsdialogbox.SelectCustomastheCategory.AddddddintotheTypefieldforthefullweekdaynameordddfortheabbreviatedweekdayname.PresstheOKbutton.Nowourdateswillappearastheweekdaynamesintheworksheet.Thedatesarestillinsidethecellsandcanbeseenintheformulabarwhenacellisselected.GettheWeekdayNamewiththeTEXTFunctionTheTEXTfunctionwillallowustoconvertnumberstotextandapplyformattingtothosenumbers.=TEXT(1234,"$#,###")WecanusetheTEXTfunctiontoconvertthenumber1234intothetextstring$1,234withtheaboveformula.Sincedatesarereallyjustserialnumbers,wecanusethisfunctiontoconvertanydateintoatextstringwiththeweekdaynameformat.TEXTSyntax=TEXT(Value,Format)Value (required)isthevaluetoconverttoatextstring.Format(required) istheformattingtoapplywhenconvertingtoatextstring.=TEXT(B2,"dddd")TheaboveformulawillconvertourdatevalueincellB2intothecorrespondingweekdayname.InthisexamplewegetavalueofFridayfromthedate2020-09-18.GettheWeekdayNumberwiththeWEEKDAYFunctionWhiletheresultsaren’tquiteasuseful,thereisalsoaWEEKDAYfunctioninExcel.Thiswillconvertadateintoacorrespondingnumberbetween1and7representingtheweekday.WEEKDAYSyntax=WEEKDAY(Date,[Type])Date(required)thedatetofindtheweekdaynumberfrom.Type(optional)theweekdaynumbertypetoreturn.Omittedor1returns1forSundaythrough7forSaturday.2returns1forMondaythrough7forSunday.3returns0forMondaythrough6forSunday.11returns1forMondaythrough7forSunday.12returns1forTuesdaythrough7forMonday.13returns1forWednesdaythrough7forTuesday.14returns1forThursdaythrough7forWednesday.15returns1forFridaythrough7forThursday.16returns1forSaturdaythrough7forFriday.17returns1forSundaythrough7forSaturday.=WEEKDAY(B2,1)TheaboveformulawillconvertourdatevalueincellB2intothecorrespondingweekdaynumber.Thesecondargumentvalueof1willreturna1forSundaythroughto7foraSaturday.Inthiscase2019-09-18returnsa6becauseit’saFriday.CombiningSWITCHwithWEEKDAYtoReturntheWeekdayNameOnitsown,theWEEKDAYfunctioncanonlyreturnanumberrepresentingtheweekday,butwecancombineitwiththeSWITCHfunctiontogettheweekdayname.=SWITCH(WEEKDAY(B2,1), 1,"Sun", 2,"Mon", 3,"Tue", 4,"Wed", 5,"Thu", 6,"Fri", 7,"Sat")TheWEEKDAYfunctionreturnsanumberfrom1to7andwecanthenusetheSWITCHfunctiontoassignaweekdaynametoeachofthesenumbers.GettheWEEKDAYNameUsingPowerQueryPowerQuery(alsoknownasGet&Transform)isapowerfuldatawranglingtoolavailableinExcel2016onward.Itmakesanydatatransformationeasyanditcangetthenameoftheweekdaytoo.Wefirstneedtoimportourdataintothepowerqueryeditor.WeneedourdatainsideanExceltable.SelectacellinsidetheExceltablecontainingthedates.GototheDatatabintheribbon.PresstheFromTable/RangecommandintheGet&TransformDatasection.Thiswillopenupthepowerqueryeditor.Wecannowtransformourdatesintothenameoftheweekday.Weneedtomakesurethecolumnisconvertedtothedatedatatype.ClickontheiconintheleftofthecolumnheadingandselectDatefromtheoptions.Withthedatecolumnselected,gototheAddColumntab.SelectDate ➜Day ➜NameofDay.=Table.AddColumn(#"ChangedType","DayName",eachDate.DayOfWeekName([Date]),typetext)ThiswilladdanewcolumncontainingtheweekdaynameandwecanseetheMcode that’sgeneratedinthepowerqueryformulabar.Thisusesthe Date.DayOfWeekNamepowerqueryfunction.AsimilarcommandcanbefoundintheTransformtab.Thedifferenceis,thiswillnotaddanewcolumn,butrathertransformtheselectedcolumn.GettheWEEKDAYNameinaPivotTablewiththeWEEKDAYDAXFunctionDidyouknowyoucansummarizetextvalueswithapivottable?Well,wecangoastepfurtherandsummarizeourdatesasalistofweekdaynamesinsideourpivottableusingaDAXmeasure!Weneedtocreateapivottablefromourdata.Selectacellinsidethedata.GototheInserttabintheribbon.PressthePivotTablecommand.IntheCreatePivotTablemenuchecktheoptiontoAddthisdatatotheDataModelandpresstheOKbutton.Thiswillcreateanewblankpivottableintheworkbookandaddthedataintothedatamodel.AddingthedatatothedatamodelwillallowustousetheDAXformulalanguagewithourpivottable.Nowwecancreateameasuretoconvertourdatesintonamesandsummarizetheresultsintoacommaseparatedlist.Selectacellinsidethepivottable.RightclickonthetableinthePivotTableFieldswindowandselectAddMeasurefromthemenuoptions.ThiswillopenuptheDAXformulaeditorandwecancreateourDAXmeasure.WecannowaddthefollowingformulaintotheDAXformulaeditor.=CONCATENATEX( Activities, SWITCH( WEEKDAY(Activities[Date],1), 1,"Sun", 2,"Mon", 3,"Tue", 4,"Wed", 5,"Thu", 6,"Fri", 7,"Sat" ), "," )GiveournewmeasureanamelikeNameofDays.AddtheaboveDAXformulaintotheformulabox.ToseetheresultsofourDAXformula,allweneedtodoisadditintotheValuesareaofourPivotTableFieldswindow.ThisisverysimilartotheWEEKDAYfunctionsolutionwithExcelfunctions.TheonlydifferenceisweneedtoaggregatetheresultswithaCONCATENATEXfunctiontodisplayinsideourpivottable.GettheWeekdayNameinaPivotTablewiththeFORMATDAXFunctionAnotherDAXfunctionwecanusetogettheweekdaynameistheFORMATfunction.ThisisverysimilartoExcel’sTEXTfunctionandwillallowustoapplyacustomformattoourdatevalues.=CONCATENATEX(Activities,FORMAT(Activities[Date],"dddd"),",")TheprocessistheexactsameasthepreviousDAXexample,butinsteadwecreateameasurewiththeaboveformula.GettheWeekdayNamewithaPowerPivotCalculatedColumnIfyouhavethepowerpivotadd-inforExcel,thenyoucanuseDAXtocreateacalculatedcolumninthedatamodel.Ifourdataisn’talreadyinthedatamodel,wecaneasilyadditbygoingtothePowerPivottabintheribbon ➜selectingAddtoDataModel.Nowwecanopenupthepowerpivotwindowbygoingtothe PowerPivottab ➜selectingManageDataModel.=FORMAT(Activities[Date],"dddd")Insidethepowerpivotwindow,wecanaddournewcalculatedcolumn.DoubleclickontheAddColumnheadingandgivethenewcolumnanamelikeWeekday.SelectacellinsidethenewcolumnandaddtheaboveDAXformulaintotheformulabarandpressenter.Whenweclosethepowerpivotwindow,wenowhavethenewWeekdayfieldavailabletouseinourpivottableandwecanadditintotheRows,ColumnsorFilterarea ofthepivottable.ConclusionsTherearelotsofoptionstogetthenameofthedayfromadateinExcel.Wecoveredformatting,Excelformulas,powerqueryandDAXformulasinthedatamodel.Thereareprobablyafewmorewaysaswell.LetmeknowinthecommentsifImissedyourfavouritemethod.AbouttheAuthorJohnMacDougallJohnisaMicrosoftMVPandfreelanceconsultantandtrainerspecializinginExcel,PowerBI,PowerAutomate,PowerAppsandSharePoint.YoucanfindotherinterestingarticlesfromJohnonhisblogorYouTubechannel.SubscribeRelatedPosts BrowseAll7WaystoRenameaSheetinMicrosoftExcelSep25,2021Keepingyourspreadsheetorganizedisimportant!Awellorganizedspreadsheet...readmore8WaystoMergeCellsinMicrosoftExcelSep8,2021Mergedcellsareoneofthemostpopularoptionsusedbybeginnerspreadsheet...readmore6WaystoCountColoredCellsinMicrosoftExcel[IllustratedGuide]Sep7,2021YouhaveprobablyusedcolorcodinginyourExceldataorseenitina...readmoreComments3Comments Meni on2019-11-20at14:42 HiJohn, here’sanothermethodtogettheweekdayname,usingtheCHOOSEfunctioncombinedwithWEEKDAY:=CHOOSE(WEEKDAY(TODAY()),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)Ofcourse,TODAY()canbereplacedbyanyvaliddate.BestRegards, MeniPorat John on2019-11-20at15:36 Yes,CHOOSEisanothergoodoption. MeniPorat on2019-11-23at02:30 TalkingabouttheWEEKDAY,itiswell-knownthatthisfunctionhastwo“bugs”andone“flaw”:The“bugs”: 1) itdoesnotreturnthecorrectdayofweekifthedateisbetween1900/1/1and1900/2/28. 2) itconsiders1900/2/29asavaliddate(whichiswrong:1900wasnotaleapyear)Theflaw: itcan’tcalculateweekdaysbefore1/1/1900. Inaddition,WEEKDAYreturnsanumber,whichmustbefurthertranslatedintoaname(whichyourpostimpressivelydoes…) Thereareseveralsolutionstotheabove-mentionedproblems.Youarekindlyinvitedtovisitmyblogandseemysolution:https://meniporat.blogspot.com/2012/07/excel-calculating-weekday-for-given-date_2236.htmlBestRegards, MeniPoratGettheLatestMicrosoftExcelTips FollowUsFollowusonsocialmediatostayuptodatewiththelatestinMicrosoftExcel!FollowFollowFollowFollow