Excel formula to get day of week from date

In this article, we'll explore different methods to extract days of the week from calendar date in Excel such as formulas and custom number formats.

When you're using Excel to manage project deadlines, schedule appointments, or analyze data trends, it's often handy to know the day names for specific dates. Excel has a variety of tools to help you figure this out. In this article, we'll show you some straightforward ways to get the day of the week from a date, whether you want it as word or number.

Get day of week from date using TEXT formula

One of the simplest ways to find the day names from a date in Excel is by using the TEXT function. Here’s how:

  1. Let's assume that the original date is in cell A3.
  2. In another cell (let's say B3), enter one of these formulas:

    To return the full name of the day such as "Monday" or "Tuesday", the formula is:

    =TEXT(A3, "dddd")

    To get a shorter version of the day names like "Mon" or "Tue", use this day of week code:

    =TEXT(A3, "ddd")

  3. Press Enter, and cell B3 will display the day name corresponding to the date in cell A3.
TEXT formula to get the day of the week in Excel

It's important to note that the TEXT function always gives you the result as text, regardless of the original cell's format. If you plan to use this output for further calculations, consider using a custom format instead of a formula.

Find day names with WEEKDAY formula

Another simple formula for days of the week in Excel is WEEKDAY.

By default, the WEEKDAY function returns a number between 1 and 7. In this system, Sunday is considered as 1, Monday as 2, and so on, but you can change this by specifying a different return_type argument.

Convert day of week to number

If your goal is to get the day of the week number, then the WEEKDAY function in its basic form is all you need:

=WEEKDAY(A1)

If cell A1 contains a date like "18-Sep-2023" (Monday), the formula will return 2; or 3 if A1 contains "19-Sep-2023" (Tuesday). WEEKDAY formula to get the day of the week number.

Convert calendar date to day of week

If you prefer having the day names, not numbers, you can combine the WEEKDAY function with the TEXT function.

For example, to convert a calendar date in cell A1 to the day of week, use one of these formulas:

=TEXT(WEEKDAY(A1), "dddd")

Or

=TEXT(WEEKDAY(A1), "ddd")

The difference is that the first formula returns the full name of the day while the second formula displays a shorter version of the name. Excel formula to get the name of the day

Return day name in any format using CHOOSE formula

If you want to create your custom labels or notations for the day names, you can do this by using WEEKDAY in combination with the CHOOSE function. Here's how it works:

  • CHOOSE returns a value from a predefined list based on an index number.
  • WEEKDAY calculates the index number corresponding to the day of the week for a given date.

In other words, this formula maps the numeric output of the WEEKDAY function to the corresponding name that you've hardcoded into the CHOOSE function.

Let's assume that your date is in cell A3, and you want to show the day names as 2-letter abbreviations such as "Su" for Sunday, "Mo" for Monday, etc. The following formula works a treat:

=CHOOSE(WEEKDAY(A3), "Su","Mo","Tu","We","Th","Fr","Sa") CHOOSE formula to show the day names in a custom format.

Display day of week from date using custom format

Another way to show the day names in Excel is by applying a custom number format. This approach allows you to keep the original date value in the cell while visually displaying the day of the week. Here's how to do it:

  1. Select the cell(s) containing the date you want to format.
  2. Right-click the selected cells to bring up a context menu, then choose Format Cells.
  3. In the Format Cells dialog box, navigate to the Number tab and select Custom in the Category list.
  4. In the Type field, enter the desired code for the day of week:
    • dddd – the code for the full day names (e.g. Sunday).
    • ddd – the code for abbreviated day names (e.g. Sun).
  5. Click OK to apply the custom format.
Custom date format for day of week

Unlike Excel formulas, this method does not change the underlying values of the cells, but only changes how they appear in cells.

In the screenshot below, you can see three columns with the same dates. Column A displays the dates in the default date format, column B shows full day names (“dddd” format), and column C shows abbreviated names (“ddd” format). If you check the formula bar, you'll notice that all three columns retain the original date values. This means you can use these dates in any calculations as fully functional Excel dates while providing a desired visual representation of the day of the week. Display days of the week from date using custom format.

These are some of the ways to get the day of week from date in Excel. You can choose any method that suits your needs and preferences. Thanks for reading, and we'll be back with more helpful tips soon!

Practice workbook for download

Excel formula for day of week - examples (.xlsx file)

Table of contents

27 comments

  1. Hi, I'm trying to identify routes for a specific postcode. but the problem is the route for each postcode differ on daily basis. Any help on which formula would work?

    • Hi! Your question is not entirely clear to me. To understand what you want to do, give an example of the source data and the expected result.

  2. Hi, i was wondering if it was possible of finding an exact date in the futur based on a day of the month. Let's say i want to find every last sunday of april in the futur. How does one go about coding that in excel if it's even possible at all

    • Hi! Set the date April 30 using the DATE function. Use the SEQUENCE function to create a sequence of years in this date. Subtract the weekday number from this date if it is less than 7 to obtain the last Sunday of the month. Define the day of the week with the help of the WEEKDAY function.

      =DATE(SEQUENCE(10,1,2024,1),4,30) - MOD(WEEKDAY(DATE(SEQUENCE(10,1,2024,1),4,30),2),7)

  3. I have a column that is formatted as ddd for days of the week. I want to highlight that row and conditionally format all Tuesday, Thursday, Saturday, and Sunday to be purple and all Monday, wednesday, and Fridays to be Blue. How do I do that? Conditional formatting is not recognizing it as a text?

    • Hello! You have changed the format of the cell, but the cell still contains a date in it. Therefore, for conditional formatting, you can use the number of the day of the week. This number can be defined using the WEEKDAY function.
      For example:

      =OR(WEEKDAY(A1,2)=2,WEEKDAY(A1,2)=4,WEEKDAY(A1,2)=6,WEEKDAY(A1,2)=7)

  4. Hi,
    I am looking at stock earnings dates and I want to return the first workday date of the previous week.

    Example : 25/04/2023 I would like to automatically return the first working day of the previous week 17/04/2023 i.e. Monday 17th April 2023.

    In some cases the first Monday of the previous week may be a public holiday so I would then like the first working day to be returned.

    I have tried to utilise the resources you list but can't quite figure out how to do it.

    Any help appreciated. Thanks

      • Thats great thanks, now I need to figure out how to interpret the logic :-) It does work though

  5. This article was very helpful.
    I use a spreadsheet to record all my trades, and it's helpful to make a note of the day number for me.
    But also, I just wanted the trading days of the week where Mon =1 - Fri =5.
    So I used: =WEEKDAY((B10)-1) where B10 is the cell with the actual date in it.
    Then I realised that any date that wasn't filled in yet, returned an error #NUM!
    So I added an IF function to get around that: =IF(B10>0.5,WEEKDAY((B10)-1),"")

    BUT I what I wanted to say, was that I wouldn't have done of any of this, were it not for your straightforward, no nonsense article.
    So thanks very much,
    John over in Blighty

  6. Hi,
    if I have a formula in column C, I need to change that formula according to the data (name) I enter in column A, or to put a specific formula in column C depending on the name I will enter in column A.

  7. Hi,
    if I have a formula in column C, I need to change that formula according to the data (name) I enter in column A, or to put a specific formula in column C deadening on the name I will enter in column A.

  8. But how do I filter from these results?
    Can't seem to be able to do that after getting the day of the week.

  9. Hi,
    if I have a date in column A, I need to display in column B the nearest Tuesday or Thursday before that day.
    Example:
    A B
    3/06/24 3/05/24
    3/8,9,10&11/24 3/07/24
    3/05/24 3/05/24
    3/07/24 3/07/24

    Thank you,

    Tamer Salib

  10. How would I go about having the date at the start of every week pop up for a specific day?

    For example, I start work every Monday and when I start a new excel file, I have to tediously input the date in multiple locations. How about instead, if I just enter a code telling excel "Input the month/day/year for the current monday."
    What would that code look like?

  11. i want to change a date to week forexample 05.03.2024 to wk 10/2

  12. So i'm trying to create a function that when a Day (one or two digit text) is input It will output the next calendar date. Example: Today is 1/8/24 and the input is 15 it will Produce 1/15/24 for the output. Or If the input is 2 it will output 2/2/24.

  13. Appreciate the guide. I used the TEXT formula and it worked but weekday column shows Saturday when Date column is empty. Is there a way to fix this/make it empty? Many thanks.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)