If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date or to convert the date into a day name, there are several options depending on your needs.
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" // "Wed" "dddd" // "Wednesday"
Excel will display only the day name, but it will leave the date value intact.
Do you want to convert the date into a day name?
If you want to convert the date value to a text value, you can use the TEXT function with a custom number format like “ddd”. The formula looks like this:
The TEXT function converts values to text using the number format that you provide. Note that date is lost in the conversion, only the text for the day name remains.
A more flexible way to get a day name from a date
If you don’t want to use a helper table, you can also map weekday number to a month name directly in the CHOOSE function like so:
In this case, enter the weekday names you want to return (abbreviated or not) as values in CHOOSE, after the first argument. WEEKDAY will extract a weekday number, and CHOOSE will use this number to return the nth value in the list. This works because WEEKDAY returns a number 1-7 that corresponds to a given day of the week. With default settings, Sunday = 1 and Saturday = 7.
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.)