How to Convert Date to Day of Week in Excel (3 Easy Ways)

Putting the Formula Together · Click on a blank cell where you want the day of the week to be displayed (B2) · Type the formula: =CHOOSE(WEEKDAY(A2),”Sun”,”Mon”,” ... Skiptocontent Menu Whenworkingwithdate-specificinformation,theremightbeinstanceswhereyouneedtofindthedayoftheweekcorrespondingtoadate. Forexample,insteadofthedate,youmaywanttoknowwhetherit’saMondayoraTuesdayoranyotherday. Thiscouldbeusefulwhenworkingwithto-dolists,daily/weeklysummaryreports,etc. Inthistutorial,wewillseethreedifferentwaysofconvertingdatestothecorrespondingdayoftheweekinExcel. Forallthethreemethodsinthistutorial,wewillbeusingthefollowingsampledataset. Wewillconvertallthedatesintheabovesampledatasetintocorrespondingdaysoftheweek. TableofContents Method1:UsingtheTEXTFunctionMethod2:UsingtheFormatCellsFeatureMethod3:UsingaMixofFormulas Method1:ConvertingDatetoDayofWeekinExcelusingtheTEXTFunction TheTEXTfunctionisagreatfunctiontoconvertdatestodifferenttextformats.Ittakesadateserialnumberordateandletsyouextractpartsofthedatetofityourrequiredformat. Here’sthesyntaxfortheTEXTfunction: =TEXT(date,date_format_code) Inthisfunction, dateisthedate,dateserialnumber,orreferencetothecellthatyouwanttoconvert. date_format_codeistheformatcodebasedonhowyouwanttheconverteddatetolook. TheTEXTfunctionappliesthedate_format_codethatyouspecifiedontheprovideddateandreturnsatextstringwiththatformat. Forexample,ifyouhavethedate“2/10/2018”incellA2,then=TEXT(A2,”dddd”)willreturn“Saturday”.Here,“dddd”isthedate_formatcodetodisplaythedayoftheweekcorrespondingtodateinfullwords. Herearesomebasicbuildingblocksfortheformatcodesthatyoucanuse: FormatCodesforDayoftheWeekorMonth: Youcanusethefollowingbasicformatcodestorepresentdayvalues: d–oneortwo-digitrepresentationoftheday(eg:3or30) dd–two-digitrepresentationoftheday(eg:03or30) ddd–thedayoftheweekinabbreviatedform(eg:Sun,Mon) dddd–fullnameofthedayoftheweek(eg:Sunday,Monday) So, ifyouapply=TEXT(A2,“d”)tothesampledataset,itwillreturn“10”. ifyouapply=TEXT(A2,“dd”),thenitwillreturn“10”. ifyouapply=TEXT(A2,“ddd”)tothesampledataset,itwillreturn“Sat”. ifyouapply=TEXT(A2,“dddd”),thenitwillreturn“Saturday”. LetusseehowwecanapplytheTEXTfunctiontooursampledatasettoconvertallthedatestodaysoftheweek. WewillfirstseehowtoconvertthedatesincolumnAtotheformatshownincolumnBintheimagebelow: BelowarethestepstoconvertdatetoweekdaynameusingtheTEXTfunction: Clickonablankcellwhereyouwantthedayoftheweektobedisplayed(B2) Typetheformula:=TEXT(A2,”ddd”)ifyouwanttheshortenedversionofthedayor=TEXT(A2,”dddd”)ifyouwantthefullversionofthedays. PresstheReturnkey. Thisshoulddisplaythedayoftheweekinourrequiredformat.Copythistotherestofthecellsinthecolumnbydraggingdownthefillhandleordouble-clickingonit. Copythiscolumn’sformularesultsbypressingCTRL+CorCmd+C(ifyou’reonaMac). Right-clickonthecolumnandfromthepopupmenuthatappears,pressPasteValuesfromthePasteOptions. Thiswillstoretheformularesultsaspermanentvaluesinthesamecolumn.NowyoucangoaheadandremovecolumnAifyouwantto. Youshouldseeallthedatesconvertedtotheshortformoftheircorrespondingdaysoftheweek: TheresultyougetincolumnBwilldifferaccordingtotheformatcodeyouusedinstep2.HerearesomeformatcodesalongwiththetypeofresultyouwillgetwhenappliedtocellA2: Function Result =TEXT(A2,“ddd”) Sat =TEXT(A2,“dddd”) Saturday Note:Usingthisformula,yourconverteddateisintextformat,soyouwillseetheresultsalignedtotheleftsideofthecells. Method2:ConvertingDatetoDayofWeekinExcelusingtheFormatCellsFeature TheFormatCellsfeatureprovidesagreatwaytodirectlyconvertyourdatestodaysoftheweekwhilereplacingtheexistingdates.Assuchyoudon’tneedtohaveaseparatecolumntotypetheformula. YoucanusetheFormatCellsdialogboxtoconvertyourdatestodaysoftheweekasfollows: Selectallthecellscontainingthedatesthatyouwanttoconvert(A2:A8). Right-clickonyourselectionandselectFormatCellsfromthepopupmenuthatappears.Alternatively,youcanselectthedialogboxlauncherintheNumbergroupundertheHometab. ThiswillopentheFormatCellsdialogbox.ClickontheNumbertab UnderCategoryontheleftsideofthebox,selecttheCustomoption. Thiswilldisplayanumberofformattingoptionsontherightside,underType.YoucantypeinyourformatcodeintheTypefield.Youcantypein“ddd”ifyouwanttheshortformor“dddd”ifyouwantthefullformoftheday. ClickOKtoclosetheFormatCellsdialogbox. Allyourselectedcellsshouldnowappearasweekdaynames. Note:ByusingFormatCells,youhavejustchangedtheformatinwhichthedatesaredisplayedinthecells.Theactualdatesareunderlyingthecellsandcanbeseenintheformulabarwhenacellisselected. Method3:ConvertingDatetoDayofWeekinExcelusingaFormula Ifyou’dratheruseyourownrepresentationforthedaysoftheweek,youcanuseaformulabasedonboththeWEEKDAYandCHOOSEfunctions. First,letusunderstandthesetwofunctionsindividually. TheWEEKDAYFunction TheWEEKDAYfunctionconvertsadatetoanumberbetween1and7,oneforeachdayoftheweek.Itchoosesthesequencingofthesenumbersbasedonthedaywewanttheweektobeginfrom. Thisishelpfulbecauseinsomecountries,theweekstartsfromSunday,whileinothers,especiallyMiddleEasterncountries,theweekstartsfromSaturday. ThesyntaxfortheWEEKDAYfunctionis: WEEKDAY(date,[type]) Here, dateistheinputdateforthefunction.Itcanbeadate,adateserialnumberorareferencetoacellthatholdsadatevalue. typeisanoptionalparameter.Ittellsthefunctionwheretobegintheweekfrom.So,ifweputa1,itmeanstheweekshouldstartfromSundayandendonSaturday.Ifweputa2,itmeanstheweekshouldstartfromMondayandendonSunday,andsoon.Bydefault,thevalueforthetypeparameterissetto1. Inourexample,WEEKDAY(A2,1)willreturn7,correspondingtoaSaturday. TheCHOOSEFunction TheCHOOSEfunctionletsyouspecifyaparticularoutcomedependingonavalue.Forexample,youcanchoosetodisplaytheword“red”ifavalueis1,“blue”ifitis2,“green”ifitis3,andsoon. Inthiswayit’sagreatalternativetousingabunchofnestedIFfunctions. ThesyntaxfortheCHOOSEfunctionis: =CHOOSE(index,value1,[value2],…) Here, indexistheinputvalue.Itcanbeanynumberbetween1and254. value1isthefirstvaluefromthelistofpossibleoutputs value2isthesecondvaluefromthelistofpossibleoutputs, etc. Thefunctionreturnsvalue1ifindexis1,value2ifindexis2,etc. Sothefunction:=CHOOSE(3,“sun”,”mon”,”tue”,”wed”,”thu”,”fri”,”sat”)willreturn“tue”,becauseit’sthethirdvalueinthelist. PuttingtheFormulaTogether LetusputthetwofunctionsCHOOSEandWEEKDAYtogethertoconvertdatetodayoftheweek.Herearethestepstofollow: Clickonablankcellwhereyouwantthedayoftheweektobedisplayed(B2) Typetheformula:=CHOOSE(WEEKDAY(A2),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”) PresstheReturnkey. Thisshoulddisplaythedayoftheweekinshortform,correspondingtothedateinA2.Copythistotherestofthecellsinthecolumnbydraggingdownthefillhandleordouble-clickingonit. Copythiscolumn’sformularesultsbypressingCTRL+CorCmd+C(ifyou’reonaMac). Right-clickonthecolumnandformthepopupmenuthatappears,pressPasteValuesfromthePasteOptions. Thiswillstoretheformularesultsaspermanentvaluesinthesamecolumn.NowyoucangoaheadandremovecolumnAifyouwantto. Here’swhatyoushouldgetastheresult: HowdidthisFormulaWork? Tounderstandhowthisformulaworked,weneedtobreakitdown: First,weusedtheWEEKDAYfunctiontofindthenumbercorrespondingtothedayoftheweek,assumingourweekstartsfromSunday.Thiswillreturnanumberbetween1and7. WeusedtheoutputoftheWEEKDAYfunctiontofindthestringfromthelistofdays[“sun”,”mon”,”tue”,”wed”,”thu”,”fri”,“sat”]correspondingtothatnumber.SoiftheoutputforWEEKDAY(A2)was7,thenthecorrespondingoutputfortheCHOOSEfunctionwillbetheseventhiteminthelist,whichis“sat”. ThuswegetthedaycorrespondingtothedateinA2(inourexample)tobe“sat”. Note:Theaboveformulawillgiveyourconverteddateintextformat,soyouwillseetheresultsalignedtotheleftsideofthecells. Youcouldalsohaveanyotherformatforthedaysinthelist,forexample,ifyouwantedtodisplayjustthefirsttwolettersforthedays,youcouldhavewrittentheformulainstep2as: =CHOOSE(WEEKDAY(A2),"Su","Mo","Tu","We","Th","Fr","Sa") Soyoucouldgetanoutputas: Ifyouwantedtomakeato-dolist,youcouldevenhaveanactivityforeachdayoftheweek.Forexample,youcouldusetheformula: =CHOOSE(WEEKDAY(A2),"cycling","rollerblading","swimming","skiing","art","crafts","freetime") Soyoucouldgetanoutputas: Inthistutorial,wesawthreewaystoconvertadatetothedayoftheweek. Thefirstmethodinvolvesafunction(TEXT),thesecondmethodinvolvesacommonlyusedExceldialogbox(FormatCells)andthethirdmethodinvolvesanExcelformula(withWEEKDAYandCHOOSEfunctions). Wehopeyoufoundthistutorialhelpfulandeasytoapplytoyourdata. OtherExceltutorialsyoumayfinduseful: HowtoConvertSerialNumberstoDateinExcel HowtoConvertDatetoMonthandYearinExcel HowtoAddDaystoaDateinExcel HowtoSortbyDateinExcel(SingleColumn&MultipleColumns) WhyareDatesShownasHashtagsinExcel? HowtoConvertMonthNumbertoMonthNameinExcel HowtoConvertDaystoYearsinExcel(SimpleFormulas) MostPopularArticlesHowtoCompareTwoColumnsinExcel(usingVLOOKUP&IF) MultipleIfStatementsinExcel(NestedIfs,AND/OR)withExamples HowtoRemoveDashes(-)inExcel? HowtoCentertheWorksheetHorizontallyonthePageinExcel HowtoMergeFirstandLastNameinExcel HowtoRemoveCommasinExcel(fromNumbersorTextString) WhydoesExcelOpenonStartup(andHowtoStopit) HowtoRemoveApostropheinExcel HowtoUnsortinExcel(RevertBacktoOriginalData)