Excel Formula: Get day name from a date in Excel

Here are a few methods to get a day name (Monday, Tuesday, Wednesday etc) from a date field in Excel. If you prefer video, see the tutorials below.

Use Excel TEXT formula

The Excel Text formula will extract the day in a specified text type format. Here is how it works.

The formula for this is =TEXT(cell,”dddd”)

Short Day Name for Date

An alternative text for this is a short name for the day (Mon, Tue, Wed, etc). To achieve that, use the formula =TEXT(cell,”ddd”)

Day Month and Year Names for Date

The TEXT function can be used for pulling name of other components of the date. For example, you can use it to extract the Month or the Year. Here are a few examples

Use the WEEKDAY formula to get the day number

Use WEEKDAY formula if you want the day number in the week. (Day 1, Day 2 etc). This formula also gives you the flexibility to define the start of your week as preferred (Day 1 is Monday, Day one is Sunday etc)

The formula is =WEEKDAY(cell, [return_type])

By default, WEEKDAY returns 1 for Sunday and 7 for Saturday.

Define the Start of the Week

The optional parameter is [return_type] and it lets you make your choice for the start of the week.

The result of this function is this

Convert the day number to a Day Name using VLOOKUP

When using WEEKDAY() formula you get a number (the day number if the week). If you want to the name of the day (Monday, Tuesday etc) then you need to perform an additional operation. We need to do a VLOOKUP on the number in order to find the corresponding day.

For that to work, we need a reference table like this

So we need to pull up the corresponding day name from the table for the number 6. To do that we will write a VLOOKUP function to lookup for the value in B2 in the E:F range


Combining formulas

In order to save space and reduce the number of elements on the page, it might be useful sometimes to combine formulas. The two formulas we used to get the Day of the week were:

in Cell B2 =WEEKDAY(A2)
in Cell C2 =VLOOKUP(B2,E:F,2,FALSE)

Notice that the second formula is using the result of the first formula saved in B2. So we can combine the two formulas like this


Writing it like this we replaced the B2 in the second formula with the actual formula we used to get the value in B2. This would help us eliminate a step and cleaning up the worksheet from unnecessary items