The TEXT function converts values to text using the number format that you provide. Note that the date is lost in the conversion, only the text for the day name remains.
Do you just want to display the day name?
If you only want to display a day name, you don't need a formula – you can use a custom number format. Select the date, then go to Format cells (Ctrl + 1 or Cmd + 1) > Custom, and enter one of these custom formats:
"ddd"// i.e."Wed""dddd"// i.e."Wednesday"
Excel will display only the day name, but it will leave the date value intact.
Handling an empty date
If you use the formula above with an empty cell, you'll get "Sat" as a result, since zero in the Excel date system is treated as "00-Jan-1900", which is a Saturday. To work around this issue, you can adjust the formula like this:
In this case, enter the weekday names you want to return (abbreviated or not) as values in CHOOSE, after the first argument. The WEEKDAY function will return a number between 1-7, and CHOOSE will use this number to return the corresponding value in the list. With default settings, Sunday = 1 and Saturday = 7. See WEEKDAY for more info.
CHOOSE is more work to set up, but it is also more flexible, since it allows you to map a date to any values you want (i.e. you can use values that are custom, abbreviated, not abbreviated, different language, etc.)
The DAY function takes just one argument, the date from which you want to extract the day. In the example, the formula is: = DAY ( B5 ) B5 contains a date value for January 5, 2016. The DAY function returns the number 5 representing the day...
In this example, the goal is to get and display the month name from any given date. There are several ways to go about this in Excel, depending on whether you want to extract the month name as text, or just display a valid Excel using the month name...
The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas...
The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.