Following are 4 different methods in Excel to find the ‘Day Name’ corresponding to a ‘Date’.
Table of Contents
1. TEXT Function to find Day Name from Date
The TEXT function in Excel can be used to return the corresponding Day Name, Month or Year from a Date.
The following formula will return Tuesday, the Day name corresponding to the Date 28-12-1937=TEXT(“28-12-1937″,”DDDD”)
With TEXT function, “DDDD” will return Tuesday and “DDD” returns the first 3 letters of the Day name, Tue.
2. Formula using CHOOSE and WEEKDAY Functions
When a Date is supplied to the WEEKDAY function in Excel, it returns a number from 1 to 7 which represents the corresponding day of the Week.
The following formula will return 3, the Day Name corresponding to the Date 28-12-1937
=WEEKDAY(B3)
This integer returned by the WEEKDAY function can be used inside the CHOOSE function to return the Name of Day corresponding to a Date.
The following formula will return Tue, the Day name corresponding to the Date 28-12-1937
=CHOOSE(WEEKDAY("28-12-1937"),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
3. Custom Number Formatting
In this method, instead of extracting the Day Name, we will display the same, in the cells using Custom Number Formatting feature of Excel.
Right-click on the cells containing Dates > Format Cells > In the Number tab of the Format Cells dialog, select Custom from the options listed under Category and type in ‘dddd’. For the first 3 letters of the day name, use ‘ddd’.
4. Power Query
Take the table containing dates into the Power Query Editor of Excel > Select the column containing Dates > go to the Transform tab of the Power Query Editor > Click on the split button for Date > Day > Name of Day
Dates will be converted into corresponding Day Names.
For a new column containing Day Names corresponding to Dates, go to the Add Column tab > Click on the split button for Date > Day > Name of Day
A new column will be created with weekdays corresponding to the dates.
To load this table into the Excel Worksheet, In the Home tab of the Power Query Editor, Close and Load.