How to convert date to weekday, month, year name or number ...

Convert a date to weekday/month name with CHOOSE function Note:TheotherlanguagesofthewebsiteareGoogle-translated.BacktoEnglishLogin  LogintoyouraccountLoginRememberMeForgotyourpassword?orPasswordResetPleaseentertheemailaddressforyouraccount.Averificationcodewillbesenttoyou.Onceyouhavereceivedtheverificationcode,youwillbeabletochooseanewpasswordforyouraccount.SubmitRegister  orExtendOfficeProductsOfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookDownloadOfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookHowtoInstallorUninstallEndUserLicenseAgreementPurchaseOfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookResourcesTips&TricksforExcelTips&TricksforWordTips&TricksforOutlookExcelFunctionsExcelFormulasExcelChartsOutlookTutorialsSupportOnlineTutorials OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookNewsandUpdates OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookSearchSearchformoreAboutUsOurteamUserTestimonialsCustomerssayGetHelp? RetrieveLicenseLostlicense?ReportaBugBugreportForumPostinforumContactUsTalktousviaEmailLanguages简体中文繁體中文FrançaisDeutschPortuguêsItalianoČeština‎DanskNederlandsالعربيةՀայերենΕλληνικάMagyarBahasaIndonesiaGaelige日本語한국어PolskiRomânăРусскийSlovenščinaEspañolSvenskaไทยTürkçeУкраїнськаTiếngViệtCymraegExtendOfficeHowtoconvertdatetoweekday,month,yearnameornumberinExcel?Saysyouenteradateinonecell,anditshowsas12/13/2015.Isthereawaytoshowonlythemonthortheweekday,orthetextofmonthnameorweekdayname,suchasDecember,orSunday?ThefollowingmethodscanhelpyoueasilyconvertorformatanykindsofdatetodisplayonlytheweekdaynameormonthnameinExcel.Convertdatestoweekday/month/yearnameornumberwithFormatCellConvertdatestoweekday/monthnamewithTEXTfunctionConvertadatetoweekday/monthnamewithCHOOSEfunctionConvertdatestoweekday/month/yearnameornumberwithanamazingtoolConvertdatestoweekday/month/yearnameornumberwithFormatCellWecancustomizethedateformattinganddisplaydatesasweekdaynamesormonthnamesonlyeasilyinExcel.1.Selectthedatecellsthatyouwanttoconverttodayofweek,month,oryearnames/numbers,rightclickandselecttheFormatCellsfromtheright-clickingmenu.KutoolsforExcelStandoutfromtheCrowd300HandyToolsSolve 80%ProblemsinExcelFreeTrialNowSaygoodbyetoterribleVBAandformulas!2.IntheopeningFormatCellsdialogbox,underNumbertabclicktheCustomintheCategorybox,andthenenter"ddd"intotheTypebox.Note:The"ddd"willdisplaydateasweekdaynamesuchas"Sat".Takethedate3/7/2019forexample,thefollowingtablewillshowothercustomdateformatting: ABCD1Date3/7/2019  2    3No.DisplayasFormattingCodeExampleShows41WeekdayNamedddThu52WeekdayNameddddThursday63MonthNamemmmMar74MonthNamemmmmMarch85MonthNumberm396YearNumberyyyy2019107YearNumberyy19118DateNumberd7129DateNumberdd73.ClicktheOKbuttontoapplythecustomdateformatting.Oneclicktoconvertmultipledatestoweek/month/yearnamesornumbersinExcelHaveyoueverconvertedadatetothedayofweekbyrightclickingandspecifyformattingcodeintheFormatCelldialog?Here,withKutoolsforExcel'sApplyDateFormattingfeature,youcanquicklyshowaseriesofdatesasonlymonthnames,ordayofweekseasilywithonlyoneclickinExcel!FullFeatureFreeTrial30-day!KutoolsforExcel-Includesmorethan300handytoolsforExcel.Fullfeaturefreetrial30-day,nocreditcardrequired!GetItNowConvertdatestoweekday/monthnameornumberwithTEXTfunctionMicrosoftExcel'sTEXTfunctioncanhelpyoutoconvertadatetoitscorrespondingmonthnameorweekdaynameeasily.Inablankcell,pleaseenterthisformula=TEXT(A2,"mmmm"),inthiscaseincellC2.,andpresstheEnterkey.Andthendragthiscell'sAutoFillhandletotherangeasyouneed.Andthedatehavebeenconvertedtomonthname.Seescreenshot:Note:Youcanchangethe"mmmm"tootherformatcodeaccordingtoabovetable.Forexample,youcanalsoconvertadatetotheweekdaynamewiththeformula=TEXT(A2,"dddd").DoyouwanttoStandoutfromthecrowdnow?30+DatefeaturesraiseyourExpertise!With30+datefeaturesofKutoolsforExcel,youwillgetpracticalskillsaboutdatesin3minutes,andworkfasterandbetterthanothers,easilygetpayraiseandpromotion!TobeMorelikeableEfficientlydealwithdateproblemsinExcel,helpyoueasilygetappreciationofothersinwork.TakecareyourfamilySaygoodbyetorepetitiveandtrivialdateworkinExcel,savemoretimetoaccompanyyourfamily.EnjoyhealthylifeInsert,modify,orcalculatedatesinbulk,reducehundredsofclickseveryday,farewelltomousehand.NeverworryaboutlayoffsImprove91%workefficiency,solveyour95%dateproblemsinExcel,finishworkaheadofschedule.Freeupyourmemory12Kutoolsformulasaboutdates,stopmemorizingpainfulformulasandVBAcodes,workwithease.KutoolsforExcelbrings300handytoolsfor1500workscenarios,only$39.0butworthmorethan$4000.0Exceltrainingofothers,saveeverypennyforyou!Choiceof110,000+highlyeffectivepeopleand300+famouscompanies.Fullfeaturefreetrial30-day,nocreditcardrequired!      ReadMore      DownloadNowConvertadatetoweekday/monthnamewithCHOOSEfunctionIfthesespecialformattingcodesarehardtorememberandapplyinformulas,youcanalsoapplytheCHOOSEfunctiontoconvertadatetothenameofmonthordayofweekinExcel.Pleasedoasfollows:Inablankcell,pleaseentertheformula=CHOOSE(WEEKDAY(B1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat"),andpresstheEnterkey.Thisformulawillconvertthedatetothedayofweekasbelowscreenshotshown.Note:Forconvertingadatetothenameofmonth,pleaseapplythisformula=CHOOSE(MONTH(B1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")Convertdatestoweekday/month/yearnameornumberwithKutoolsforExcelTheTEXTfunctioniseasytodealwithafewdates,anditwillbetime-consumingifmanyones.KutoolsforExcel'sApplyDateFormattingtoolcanhelpyouconvertalldatesinselectionstothemonthnameorweekdaynameeasily.KutoolsforExcel-Includesmorethan300handytoolsforExcel.Fullfeaturefreetrial30-day,nocreditcardrequired!GetItNow1.Selecttherangethatyouwillworkwith,andclickKutools>Format>ApplyDateFormatting,seescreenshot:2.IntheApplyDateformattingdialogbox,pleaseselectthedateformattingintheDateformattingbox,andclicktheOkbutton.Forexample,youcanselect03,MarorMarchintheDateformattingboxtoconvertthedatestomonthnamesormonthnumbers,orselectWedorWednesdaytoconvertthedatestoweekdaynames,orselect01or2001toconvertdatestotheyearnumbers,orselect14toconverttodatenumbers.Nowallselecteddatesareconvertedtothespecifieddateformatting,suchasnameofmonth,dayofweek,orothersasyouchoose:Note:TheApplyDateFormattingtooldoesnotchangetheactualvalues.ThisApplyDateFormattingfeaturewillhelpExcelusersoneclicktoconvertallselecteddatestothemonthnames,dayofweeks,year,etc.HaveaFreeTrial!RelatedarticlesInsertcurrentdayormonthoryearintocellinExcelAdd/subtractdays/months/yearstodateinExcelSumvaluesbetweentwodatesrangeinExcel300toolshelpyoustandoutfromthecrowdnowwithKutoolsforExcel-neverworryaboutjobcutKutoolsforExcelbrings300handytoolsfor1500workscenarios,helpsyouworkfasterandbetterthanyourcolleagues,andeasilywintrustofyourboss.Youwillbethelastoneinthelistoflayoffs,andeasilymaintainastableandbetterlifeforyourfamily!TobeamasterofExcelin3minutes,andeasilywinappreciationofothers.Improve80%workefficiency,solveyour80%problemsinExcel,notworkovertime.Speedupyourwork,save2hourseverydaytoimproveyourselfandaccompanyfamily.SaygoodbyetocomplicatedformulasandVBAcode,freeupyourmemory.Reducethousandsofkeyboardoperationsandmouseclicks,farawayfrommousehand.Spend$39.0,worthmorethan$4000.0trainingofothers.Choiceof110,000+highlyeffectivepeopleand300+famouscompanies,workstableinExcel.Fullfeaturefreetrial30-day,nocreditcardrequired.ReadMore...FreeTrialNow 49CommentsLoginSortbyNewestBestPopularNewestOldestSaysomethinghere...symbolsleft.YouareguestLoginNoworpostasaguest,butyourpostwon'tbepublishedautomatically.PostLoadingcomment...Thecommentwillberefreshedafter00:00.Topostasaguest,yourcommentisunpublished.Rose·8monthsago InCell1,MonthismentionedinTextformatandhowdoIfindthenumberofdaysforthesamemonthinCell2ReplyTopostasaguest,yourcommentisunpublished.Juss·1yearsago HOWDOICONVERT2NDMONDAY2019TOADATE?ReplyTopostasaguest,[email protected]·2yearsago Supposetodayis25.10.19thatisFridayandIhavetomakeanykindofstatementinexcelwhereIneedeverydaytoenterpreviousdaydateoryoucansayonedaybackdatethatis24.10.19whichisThursdayinoneofthecellinexcel.SoItriedfordateoneformulathatis=Today()-1,soitbecomes24.10.19butIdon'tknowhowtoputformulaforweeklyday,CanAnyonehelpmeoutReplyTopostasaguest,yourcommentisunpublished.kellytte·2yearsago =CHOOSE(WEEKDAY(TODAY()-1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")ReplyTopostasaguest,yourcommentisunpublished.Daniel·2yearsago ifIhave4thThursdayofDec2019,howwouldIcalculatethedateinexcel,whatwillbetheformullaReplyTopostasaguest,yourcommentisunpublished.kellytte·2yearsago HiDaniel, First,definethedayofweekday.Ingeneral,wecanuse1representsSun,2representsMon,…,and7forSat. Second,theYearandMontharefixed(2019Dec) Nowwecanusetheformula=DATE(B3,C3,1+E3*7)-WEEKDAY(DATE(B3,C3,8-VLOOKUP(D3,B6:C12,2,FALSE)))toreturnthespecifieddate.Seescreenshot: Note:B3istheyear,C3isMonth,E3indicatesthenthdayofweek,D3isthedayofweek,B6:C12isthetablewherewedefinethedayofweeks.ReplyTopostasaguest,yourcommentisunpublished.Saran·2yearsago Hi, HowcanIconvertdayandtime(IST)toPST?Forexample,SUN6:00AM(IST)incolumnA2,IneedthevalueforPSTwhichisSUN7:30PM. ReplyTopostasaguest,yourcommentisunpublished.kellytte·2yearsago HiSaran, YoucanuseKutoolsformula–Addminutestodate:add810minutes(13.5hours)totheISTtime,andgetthePSTtime. ReplyTopostasaguest,yourcommentisunpublished.Annastacia·3yearsago 02/01/201600:00iHavedateinthisformatandiwanttoconvertittothedaysoftheweek.....mondaytuesday,wednesdayetc.KindlyhelpReplyTopostasaguest,yourcommentisunpublished.kellytte·3yearsago Hi, Both=TEXT(A1,"dddd")and=TEXT(A1,"ddd")canconvertthedateswithtimetodaysofweek.Trythem! ReplyTopostasaguest,yourcommentisunpublished.B·3yearsago IfIhaveadateina1(1/25/18)andIwanta2togivethemonth(Jan)butmymonthsfromthe25th-26thofnextmonth,ie;12/26/17-1/25/17wouldbeJan,and1/26/18-2/25/18wouldbeFeb.Soinmycaseifa1is1/27/18wouldmakea2sayFeb.WhatformulacouldIuse?Ican'tfindanythingaboutsettingyourowndaterangetoreflectacertainmonth,forlikebillingcyclesforinstance.Pleasehelp!!ReplyTopostasaguest,yourcommentisunpublished.kelly001·3yearsago Hi, youcantrythisformula=IF(DAY(A1)>25,TEXT(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)),"MMMM"),TEXT(A1,"MMMM"))ReplyTopostasaguest,yourcommentisunpublished.owen·4yearsago idliketoask,howtocomputeforthe#ofdaysoutstandingbasedonthecutoffdate:e.g.07.21.17(cuttoffdateReplyTopostasaguest,yourcommentisunpublished.·4yearsago HiOwen, Doyoumeancalculatedaysfromtodaytothedeadline?Ifso,youcantrythisformula=deadlinedate-TODAY()ReplyTopostasaguest,yourcommentisunpublished.pavan·4yearsago hello,howcanbeshowmonthperiodinthisformatelike1march2017to31march2017,plshelpReplyTopostasaguest,yourcommentisunpublished.·4yearsago maybethisformula=TEXT(A1,"dmmmmyyyy")canhelpyouReplyTopostasaguest,yourcommentisunpublished.Aahana·4yearsago Hi,Iwanttoaddacolumn"Month"thatdisplaysmonthofthedatementionedin"Date"columnandtherearealmost11,000rows.Pleasehelp.text()functioncannotbeappliedtoeverycolumnindividually.ThankyouReplyTopostasaguest,yourcommentisunpublished.Charlie·4yearsago [quotename="Aahana"]Hi,Iwanttoaddacolumn"Month"thatdisplaysmonthofthedatementionedin"Date"columnandtherearealmost11,000rows.Pleasehelp.text()functioncannotbeappliedtoeverycolumnindividually.Thankyou[/quote]Hi,onaseparatesheetintheworkbookcreateatableA1-A12fill1,2,3etc,incolB1-12typethemonthnames..jan,feb,maretc..RenametheSheetto:MonthsInnamemanagercreateanewname,callthis"month"andreferto=Months!$A$1:$B$12Onthesheetyouwanttoshowthemonth,insertacolumnafterthedate,andtypethefollowingformula:=IF(A1="";"";VLOOKUP(Month(A1);month;2))A1shouldbereplacedwiththereferenceofthecellthatcontainsthedate!Thenyoucancopytheformuladowninthecolumn.Ifthedatesinyoursheetareinformatyyyy-mm-ddthenthisformulawillreturnAprilfor2017-04-18Hopethishelps.ReplyTopostasaguest,yourcommentisunpublished.eyvii·4yearsago howicandisplaythedatelikethisinexcel23thdayofMarch24,2018?ReplyTopostasaguest,yourcommentisunpublished.test·3yearsago doyoumean23rddayofMarch,2018ReplyTopostasaguest,yourcommentisunpublished.ha·5yearsago anyonepleasehelpmetogetonlyweekslikeiwanttoshowdatagroupbyweekanditneedtoshowalwaysSundayandweekgroupexample:1/1,1/8,1/15ReplyTopostasaguest,yourcommentisunpublished.kellytte·3yearsago Hi, youcanapplytheWEEKNUMfunctiontoreturntheweeknumberofspecifieddate.forexample,=WEEKNUM("2018/5/27",1)willreturn22,itmeans2018/5/27isinthe22edweekofYear2018. Andthenyoucangroupdatesbytheirweeknumbers.ReplyTopostasaguest,yourcommentisunpublished.MikeDelaney·5yearsago WhenIusetheformulaincolumnK=CHOOSE(WEEKDAY(J4),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")thencopyitdownthecolumn,anyspaceincolumnJthatisblankcomesupwithSaturdayincolumnK.IneedawayforifacellisablankincolumnJforittostayblankinKaswell.ReplyTopostasaguest,yourcommentisunpublished.Charlie·5yearsago [quotename="MikeDelaney"]WhenIusetheformulaincolumnK=CHOOSE(WEEKDAY(J4),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")thencopyitdownthecolumn,anyspaceincolumnJthatisblankcomesupwithSaturdayincolumnK.IneedawayforifacellisablankincolumnJforittostayblankinKaswell.[/quote]Hi,trythefollowingformulainK4...=if(J4="","",CHOOSE(WEEKDAY(J4),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"))WhencopieddownincolumnKifcellsincolumnJisemptyit'llstayblankincolumnK.ReplyTopostasaguest,yourcommentisunpublished.Marilyn·5yearsago Mytablecameoverwiththedateas20160502,howeverIhavetoadd45daystoalldatessothisformatdoesnotwork,isthereawaytoconvertitto05/02/2016?ReplyTopostasaguest,yourcommentisunpublished.·4yearsago HiMarilyn, =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))thisformulawillchangethestring20160502todateindefaultdateformatting. ReplyTopostasaguest,yourcommentisunpublished.Marcia·6yearsago Thanks.Thiswasincrediblyhelpful!!!ReplyTopostasaguest,yourcommentisunpublished.Maher·6yearsago Hi,Howtocreateaformulafortheflowing:1-CellA2hasfullbirthdaydate(Day,MonthandYear)2-IwantCellBtoshow"happyBirthday"messageifCellA2istoday'sbirthdaydateThanks,ReplyTopostasaguest,yourcommentisunpublished.Charlie·6yearsago Hi,1-TypethedateinA2inthedateformatofyourcomputer2-Useformula'=IF(A2=TODAY();"HappyBirthday";FALSE)inthecellyouwanttoshowHappyBirthday(cellB2)youmayneedtochange;with:intheformulaifitdoesn'twork.AlsoifyouwanttoleavecellB2emptywhenA2isnottodaythenreplaceFALSEwith"".ReplyTopostasaguest,yourcommentisunpublished.Charlie·6yearsago [quotename="Maher"]Hi,Howtocreateaformulafortheflowing:1-CellA2hasfullbirthdaydate(Day,MonthandYear)2-IwantCellBtoshow"happyBirthday"messageifCellA2istoday'sbirthdaydateThanks,[/quote]Hi,Trythisformula...=IF(C2=TODAY();"happybirthday";"")Ifitdoesn'tworkreplace;with:alsoifyouwishtoleavecellB2emptywhenbirthdayisnottodaythenreplaceFALSEwith""intheformula.ReplyTopostasaguest,yourcommentisunpublished.rupeshsharma·6yearsago Thankyou,workinggood.ReplyTopostasaguest,yourcommentisunpublished.sudha·7yearsago Thankyoualot....toshowtheweekdayformula.ReplyTopostasaguest,yourcommentisunpublished.sudha·7yearsago Niceformula...workinggoodReplyTopostasaguest,yourcommentisunpublished.Charlie·7yearsago HiusingExcel2007...entered=TEXT(A1,"mmmm")goterror...correctedformulawith=TEXT(A1;"mmmm")gettingresult00!!ifIchange"mmmm"with"dddd"itdisplaysthe"day"properly.HowIcanIgetittodisplaythe"month"?..ReplyTopostasaguest,yourcommentisunpublished.Geno·7yearsago There'sanextraspacehidinginyourformula(tryrightbeforeoraftertheclosingparenthesis).The";"shouldnotwork-the","isthecorrectformatfortheformula.ReplyTopostasaguest,yourcommentisunpublished.Charlie·6yearsago [quotename="Charlie"]HiusingExcel2007...entered=TEXT(A1,"mmmm")goterror...correctedformulawith=TEXT(A1;"mmmm")gettingresult00!!ifIchange"mmmm"with"dddd"itdisplaysthe"day"properly.HowIcanIgetittodisplaythe"month"?..[/quote][quotename="Geno"]There'sanextraspacehidinginyourformula(tryrightbeforeoraftertheclosingparenthesis).The";"shouldnotwork-the","isthecorrectformatfortheformula.[/quote]Helloagain,I'vedoublecheckedtheformula...nohiddenspacesetcinit.Inexcelyoucanactuallychoosebetween","and";"asoperativedividerwhichinmycaseis";"usingitintheformulafordayddddworksfinebutmmmmdoesnotreturnnameofmonth!AllIgetis00.I'vetriedonseveralcomputerswithexcelinstalled...seemstobeabugintheprogram.ReplyTopostasaguest,yourcommentisunpublished.janey·7yearsago orjust=TEXT(WEEKDAY(A1),"dddd")ReplyTopostasaguest,yourcommentisunpublished.janey·7yearsago forweekdayyoumayuse=TEXT(A1,"[$-14409]dddd")andyougetFridaydisplayed.NoneedthatKutoolsReplyTopostasaguest,yourcommentisunpublished.Girish·7yearsago thanksfortheformula..veryniceone:-)ReplyTopostasaguest,yourcommentisunpublished.Hemant·7yearsago GoodJob....Great.....itsolvemyissuesinsecondsReplyTopostasaguest,yourcommentisunpublished.Smarty·7yearsago Thanksalot:)Reallyhelpful. ReplyTopostasaguest,yourcommentisunpublished.John·7yearsago Thankyou,thissavedmeHOURSoftime!!!!!ReplyTopostasaguest,yourcommentisunpublished.hey1990·7yearsago needhowtoconvertdate(25-02-14)intomonthinexcel.alsowhilecretingthepivotifhavetodateinasamemonthshowingsamemonthintwotimesinpivot.plsReplyTopostasaguest,yourcommentisunpublished.PAttie·7yearsago Mygoodness,it'sworkingnow....sorry!ReplyTopostasaguest,yourcommentisunpublished.PAttie·7yearsago Ienteredthetextformulabutitdidnotwork...help?ReplyTopostasaguest,yourcommentisunpublished.PankajSanwaria·7yearsago Thanksalotforformula=TEXT(A1,"mmmm")toconvertdateintext.ReplyTopostasaguest,yourcommentisunpublished.PankajSanwaria·7yearsago Thanksalotforformula(=Text(Cell),"mmmm").ReplyTopostasaguest,yourcommentisunpublished.subhash·7yearsago Thanks,verymucheffectiveformulaReplyTopostasaguest,yourcommentisunpublished.JacquesLimbi·4yearsago Fantastic!Iworkedverywellformeaswell.ReallyappreciateyourhelpReplyProducts OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookDownloadOfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookHowtoInstallorUninstallEndUserLicenseAgreementPurchase OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookResources Tips&TricksforExcelTips&TricksforWordTips&TricksforOutlookExcelFunctionsExcelFormulasExcelChartsOutlookTutorialsSupportOnlineTutorials OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookNewsandUpdates OfficeTabKutoolsforExcelKutoolsforWordKutoolsforOutlookSearchSearchformoreAboutUsOurteamUserTestimonialsCustomerssayGetHelp? RetrieveLicenseLostlicense?ReportaBugBugreportForumPostinforumContactUsTalktousviaEmailLanguages 简体中文繁體中文FrançaisDeutschPortuguêsItalianoČeština‎DanskNederlandsالعربيةՀայերենΕλληνικάMagyarBahasaIndonesiaGaelige日本語한국어PolskiRomânăРусскийSlovenščinaEspañolSvenskaไทยTürkçeУкраїнськаTiếngViệtCymraeg