Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Get day name from date in Excel

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.

Worked Example:   Get month name from date in Excel

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.

Worked Example:   Convert Numbers to Text in Excel

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.

Worked Example:   Create date range from two dates in Excel

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.)

Leave a Reply

Your email address will not be published. Required fields are marked *