by If you are looking for an Excel function to get day of week from date, you've landed on the right page. This tutorial will teach you how to use the WEEKDAY formula in Excel to convert a date to a weekday name, filter, highlight and count weekends or workdays, and more.
There are a variety of functions to work with dates in Excel. The day of week function (WEEKDAY) is particularly useful for planning and scheduling, for example to determine the timeframe of a project and automatically remove weekends from the total. So, let's run through the examples one-at-a-time and see how they can help you cope with various date-related tasks in Excel.
WEEKDAY - Excel function for day of week
The Excel WEEKDAY function is used to return the day of the week from a given date.
The result is an integer, ranging from 1 (Sunday) to 7 (Saturday) by default. If your business logic requires a different enumeration, you can configure the formula to start counting with any other day of week.
The WEEKDAY function is available in all versions of Excel 365 through 2000.
The syntax of the WEEKDAY function is as follows:
Where:
Serial_number (required) - the date that you want to convert to the weekday number. It can be supplied as a serial number representing the date, as a text string in the format that Excel understands, as a reference to the cell containing the date, or by using the DATE function.
Return_type (optional) - determines what day of the week to use as the first day. If omitted, defaults to the Sun-Sat week.
Here is a list of all supported return_type values:
| Return_type | Number returned |
|---|---|
| 1 or omitted | From 1 (Sunday) to 7 (Saturday) |
| 2 | From 1 (Monday) to 7 (Sunday) |
| 3 | From 0 (Monday) to 6 (Sunday) |
| 11 | From 1 (Monday) to 7 (Sunday) |
| 12 | From 1 (Tuesday) to 7 (Monday) |
| 13 | From 1 (Wednesday) to 7 (Tuesday) |
| 14 | From 1 (Thursday) to 7 (Wednesday) |
| 15 | From 1 (Friday) to 7 (Thursday) |
| 16 | From 1 (Saturday) to 7 (Friday) |
| 17 | From 1 (Sunday) to 7 (Saturday) |
Note. The return_type values 11 through 17 were introduced in Excel 2010 and therefore they cannot be used in earlier versions.
Basic WEEKDAY formula in Excel
For starters, let's see how to use the WEEKDAY formula in its simplest form to get the day number from date.
For example, to get the weekday from date in C4 with the default Sunday - Saturday week, the formula is:
=WEEKDAY(C4)
If you have a serial number representing the date (e.g. brought by the DATEVALUE function), you can enter that number directly in the formula:
=WEEKDAY(45658)
Also, you can type the date as a text string enclosed in quotation marks directly in the formula. Just be sure to use the date format that Excel expects and can interpret:
=WEEKDAY("1/1/2025")
Or, supply the source date in a 100% reliable way using the DATE function:
=WEEKDAY(DATE(2025, 1,1))
To use the day mapping other than the default Sun-Sat, enter an appropriate number in the second argument. For example, to start counting days from Monday, the formula is:
=WEEKDAY(C4, 2)
In the image below, all the formulas return the day of the week corresponding to January 1, 2025, which is stored as the number 45658 internally in Excel. Depending on the value set in the second argument, the formulas output different results.
At first sight, it may seem that the numbers returned by the WEEKDAY function have very little practical sense. But let's look at it from a different angle and discuss some formulas that solve real-life tasks.
How to convert Excel date to weekday name
By design, the Excel WEEKDAY function returns the day of the week as a number. To turn the weekday number into the day name, employ the TEXT function.
To get full day names, use the "dddd" format code:
To return abbreviated day names, the format code is "ddd":
For example, to convert the date in A3 to the weekday name, the formula is:
=TEXT(WEEKDAY(A3), "dddd")
Or
=TEXT(WEEKDAY(A3), "ddd")
Please note that in this formula, you should use WEEKDAY with only one argument, serial_number. Do not include return_type, even if your week starts on a day other than Sunday.
Actually, the WEEKDAY function is unnecessary for this formula. The TEXT function alone would work nicely:
=TEXT(A3, "dddd")
Though, we often think of WEEKDAY as the day of week function, which might make this formula easier to remember.
Another possible solution is using WEEKDAY together with the CHOOSE function.
For example, to get an abbreviated weekday name from the date in A3, the formula goes as follows:
=CHOOSE(WEEKDAY(A3),"Sun","Mon","Tus","Wed","Thu","Fri","Sat")
Here, WEEKDAY returns a serial number from 1 (Sun) to 7 (Sat) and CHOOSE selects the corresponding value from the list. Since the date in A3 (Wednesday) corresponds to 4, CHOOSE outputs "Wed", which is the 4th value in the list.
Though the CHOOSE formula is slightly more cumbersome to configure, it provides more flexibility letting you output the day names in any format you want. In the above example, we show the abbreviated day names. Instead, you can deliver full names, custom abbreviations or even day names in a different language.
For more examples, see Excel formula to get day of week from date.
Excel WEEKDAY formula to find and filter workdays and weekends
When dealing with a long list of dates, you may want to know which ones are working days and which are weekends.
To identify weekends and weekdays in Excel, build an IF statement with the nested WEEKDAY function. For example:
=IF(WEEKDAY(A3, 2)<6, "Workday", "Weekend")
This formula goes to cell A3 and is copied down across as many cells as needed.
In the WEEKDAY formula, you set return_type to 2, which corresponds to the Mon-Sun week where Monday is day 1. So, if the weekday number is less than 6 (Monday through Friday), the formula returns "Workday", otherwise - "Weekend".
To filter weekends or workdays, apply Excel filter to your dataset (Data tab > Filter) and select either "Weekend" or "Workday".
In the screenshot below, we have weekdays filtered out, so only weekends are visible:
If some regional office of your organization works on a different schedule where the days of rest are other than Saturday and Sunday, you can easily adjust the WEEKDAY formula to your needs by specifying a different return_type.
For example, to treat Saturday and Monday as weekends, set return_type to 12, so you'll get the "Tuesday (1) to Monday (7)" week type:
=IF(WEEKDAY(A2, 12)<6, "Workday", "Weekend")
How to highlight weekends workdays and in Excel
To spot weekends and workdays in your worksheet at a glance, you can get them automatically shaded in different colors. For this, use the weekday/weekend formula discussed in the previous example with Excel conditional formatting. As the condition is implied, we only need the core WEEKDAY function without the IF wrapper.
To highlight weekends (Saturday and Sunday):
=WEEKDAY($A2, 2)<6
To highlight workdays (Monday - Friday):
=WEEKDAY($A2, 2)>5
Where A2 is the upper-left cell of the selected range.
To set up the conditional formatting rule, the steps are:
- Select the list of dates (A2:A15 in our case).
- On the Home tab, in the Styles group, click Conditional formatting > New Rule.
- In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter the above-mentioned formula for weekends or weekdays.
- Click the Format button and select the desired format.
- Click OK twice to save the changes and close the dialog windows.
For the detailed information on each step, please see How to set up conditional formatting with formula.
The result looks pretty nice, doesn't it?
How to count weekdays and weekends in Excel
To get the number of weekdays or weekends in the list of dates, you can use the WEEKDAY function in combination with SUM. For example:
To count weekends, the formula in D3 is:
=SUM(--(WEEKDAY(A3:A20, 2)>5))
To count weekdays, the formula in D4 takes this form:
=SUM(--(WEEKDAY(A3:A20, 2)<6))
In Excel 365 and Excel 2021 that handle arrays natively, this works as a regular formula as shown in the screenshot below. In Excel 2019 and earlier, press Ctrl + Shift + Enter to make it an array formula.
How these formulas work:
The WEEKDAY function with return_type set to 2 returns a day number from 1 (Mon) to 7 (Sun) for each date in the range A3:A20. The logical expression checks if the returned numbers are greater than 5 (for weekends) or less than 6 (for weekdays). The result of this operation is an array of TRUE and FALSE values.
The double negation (--) coerces the logical values to 1's and 0's. And the SUM function adds them up. Given that 1 (TRUE) represents the days to be counted and 0 (FALSE) the days to be ignored, you get the desired result.
Tip. To calculate weekdays between two dates, use the NETWORKDAYS or NETWORKDAYS.INTL function.
If weekday then, if Saturday or Sunday then
Finally, let's discuss a bit more specific case that shows how to determine the day of the week, and if it's Saturday or Sunday then do something, if a weekday then do something else.
Suppose you are calculating payments for employees who have done some extra work on their days off, so you need to apply different payments rates for workdays and weekends. This can be done using the following IF statement:
- In the logical_test argument, nest the WEEKDAY function that checks whether a given day is a workday or weekend.
- In the value_if_true argument, multiply the number of working hours by the weekend rate (G4).
- In the value_if_false argument, multiply the number of working hours by the workday rate (G3).
The complete formula in D3 takes this form:
=IF(WEEKDAY(B3, 2)>5, C3*$G$4, C3*$G$3)
For the formula to copy correctly to the below cells, be sure to lock the rate cell addresses with the $ sign (like $G$4).
WEEKDAY function not working
Generally, there are two common errors that a WEEKDAY formula may return:
#VALUE! error occurs if either:
- Serial_number or return_type is non-numeric.
- Serial_number is out of supported dates range (1900 to 9999).
#NUM! error occurs when return_type is out of the permitted range (1-3 or 11-17).
This is how to use the WEEKDAY function in Excel to manipulate days of week. In the next article, we will explore Excel functions to operate on bigger time units such as weeks, months and years. Please stay tuned and thank you for reading!
Practice workbook for download
WEEKDAY formula in Excel - examples (.xlsx file)
284 comments
Request your help in below scenarios
1. If my month start with Monday then from Monday to Sunday to be marked with Week1 Next Monday to Sunday will be Week2 next Monday to Sunday will be Week3 next from Monday to till end of month to be marked as Week3
2. If month doesn't start with Monday then from day 1 to 2nd Sunday of the month to be marked as week1 next Monday to Sunday to be marked as week2 next Monday to Sunday to be marked as week3 next Monday to end of the month as week4
I'm SUPER new to this!
I'm trying to figure out how to show a Weekday, Month, Day of the Month, & Year (Wednesday July 13, 2022) by entering 7/13/22 in a cell.
I have it in one now. But if I copy & paste it, it shows the date that I copied. I need it to be blank, so I can enter a numeric date, then it transposes it to the format I mentioned above.
Something for a cell to select an AM/PM time of day too, would be appreciated!
PLEASE & THANK YOU!
Hello!
Enter the date in the cell and then set the custom date format as you need. You can find the examples and detailed instructions here: How to change Excel date format and create custom formatting. I hope it’ll be helpful.
Good day.
I would like to count the days in a range.
eg.
(a1) july 7, 2022 - (a2) july 9, 2022
(b1) july 8, 2022 - (b2) july 10, 2022
Results should be:
Thursday - 1
Friday - 2
Saturday - 2
Sunday - 1
Thanks
Hi!
You want to count the days of the week when only the first and last date in a date range is specified. I'm really sorry, looks like this is not possible with the standard Excel options.
Hello!
Seeking your kind assistance. I need to separate the sum of the Monday to Friday sales from the weekend sales per week of the month for the whole year. Is there an easier way to do this other than sum function?
Hi!
To find a conditional sum, use the SUMPRODUCT function.
=SUMPRODUCT(--(WEEKDAY(A1:A100,2)<6),B1:B100)
I hope my advice will help you solve your task.
Thank you Mr. Trifuntov!
Be careful!
=DAYS(01/07/2022,01/08/2022) gives 31, the number of days in July, so this is BETWEEN the dates: 01/08/2022 not being counted.
=NETWORKDAYS(01/07/2022,01/08/2022) gives 22. that is the number of workdays in July 2022 plus the Monday 1 August 2022. So, in the NETWORKDAYS (and NETWORKDAYS.INTL) the end-date is included in the period.
Be aware of this if you have to calculate the workdays in a period as a fraction of the number of days in that period, this can be dealt with by simply adding 1 to the DAYS function.
Hi!
Keep in mind that the date 01/08/2022 actually means 01/08/2022 00:00:00 in Excel and does not include that day. We have written about this many times in our blog.
Thanks, but that's not really the issue here (and indeed well known); the issue is that the NETWORKDAYS function does include the end-date in the evaluation of workdays, which at least is inconsistent with the DAYS function.
Hi!
It is not possible to set an Excel filter on the days of the week in the current column.
You can use WEEKDAY and FILTER function to get a list of dates in a new place.
Alternatively, use an additional column with the WEEKADY function to set a filter on that column.
Sorry, your reply does not relate to my point.
I have the below calendar formula and have been searching for days and days to insert a ceiling function to highlight every other Friday and have not been successful. "P9" is a cell that has "July 1, 2022" in it. Please help. Thanks.
=TEXT(SEQUENCE(54,7,-MOD(WEEKDAY(P9,2),7)+1)," [>"&DAY(EOMONTH(P9,0))&"];;#")
Hi!
I don't really understand what you want to do. To get the July calendar, try changing 1 to 2 in the formula. If this is not what you wanted, please describe the problem in more detail.
Hi, I would like to exclude weekend in my column, instead of i filter out weekend and weekday in new column, is there any ways to just filter in those column
Hi!
In cell A1, write the starting date. In cell A2, write the formula and copy it down along the column:
=IF(WEEKDAY(A1+1,2)>5,A1+3,A1+1)
Hope this is what you need.
I have a spreadsheet with SUNDAY-SATURDAY tabs at the bottom for each day. I would like to be able to put the date in the SUNDAY tab and it auto-populate the date for the remaining 6 tabs (Mon-Sat). Is that possible? I've tried a few different formulas and can't figure it out.
I am currently just going to each tab and changing the daily dates each week. It would be nice to just change Sunday and it populate the rest for me.
Thanks!
To be more clear... I am not trying to change the name of the tabs itself. I have a cell in each tab for that days date that I want to auto-populate.
Hi!
Here is the article that may be helpful to you: Adding days to a date in Excel.
Thanks! This was exactly what I needed! I was making it way more difficult than just =SUN!C1+1 for the date cell in the Monday tab, then +2 for the cell in the Tuesday tab, etc..
Hello!
I don't quite understand where you want to put the date. If the date is written in a cell, then every next day means adding 1 to the date. If you want to change tab names, you can only do it manually or use a VBA macro.
Is there say week 18 and Tuesday shown as 18.2 and plus 4 days show 18.6. After 18.7 next is week 19. Any formula to do this.
Hello!
Use WEEKNUM and WEEKDAY function:
=WEEKNUM(B1)&"."&WEEKDAY(B1,2)
This should solve your task.
Please help. I track shipments per week of the year (week Num). The current capacity is shipping two orders per week. If there are more than two orders per week listed, what formula do I need so the orders greater than two are moved into the following week?
Thanks for your help.
Hello!
In a separate column, determine the week number using the WEEKNUM function. Then use the COUNTIF function to count the number of times the desired week number occurs.
I hope my advice will help you solve your task.
Can anyone help with what is wrong in this formula
=COUNTIFS(B2:B5,">=EOMONTH(TODAY(),-2) +1",C2:C5,"N/A",D2:D5,"<=EOMONTH(TODAY(),-2) +28")
it's return value is just zero. please help me.
here is the data:
B2:B5 =
01/02/2022
01/01/2022
01/02/2022
01/01/2022
C2:C5 =
04/09/2022
N/A
30/03/2022
N/A
D2:D5 =
28/02/2022
31/01/2022
03/03/2022
30/01/2022
Hello!
Your formula contains errors. Read carefully how to use COUNTIFS function with dates.
=COUNTIFS(B2:B5,">="&(EOMONTH(TODAY(),-2)+1), C2:C5, "N/A", D2:D5,"<="&(EOMONTH(TODAY(),-2)+28))
The formula returns 0 because all dates in column D are greater than 28-Jan-22
How do i formulate it to say yes if it is a weekday and no if it is a weekend?
Hello!
Use the WEEKDAY function to determine the day of the week. Then use that in your IF formula.
=IF(WEEKDAY(TODAY())>5,"Yes","No")
I hope my advice will help you solve your task.
Hi! Given a column of dates in the form, month, followed by the year...such as...
Jan-2000
Feb-2000
Mar-2000
.
.
.
December-2000
How can I separate the month from the year? I would like the months in one column, followed by its year in the next column. The goal is to create fields or "month" and "year" in a pivot table. Thank you.
Sue
Hello!
You can select the month from the date using the MONTH function. Highlight the year - using YEAR function. Or use a custom date format.
This should solve your task.
Greetings from Ireland, is it possible to configure the WEEKNUM formula so that it yields a day of the Mon-Fri works well? For example, Monday would be "XX.1", Tuesday would be "XX.2", etc.?
Thanks for your help. David
Hello!
I don't really understand what you want to calculate.
However, I’ll try to guess and offer you the following formula:
=WEEKNUM(A4)&"."&WEEKDAY(A4,2)
If this is not what you wanted, please describe the problem in more detail.
Hey, I was wondering if you could possibly help me.
I am trying to figure out the amount of days late a tenants rent is. I am using this formula but it is not working, am I doing it wrong?
=DAYS((today(),datevalue(2021/06/08))
Basically, I just need to know if the tenant's rent is due on the 8th of any given month (for this we can say June 8 2021) and by today the rent still is not paid how many days late are they, including today. I mean I can do the math and I know it would be 178 days late, but is there a formula to put in the cell so it will calculate it automatically? Also, I think that if I just change the month in the following cells (ex July 8, 2021, Aug 8 2021) it would still work? Yes?
Thank you in advance, any help you can offer would be super. I think if I were to use such a formula it would be super useful when I am sending the tenant reminder letters that their rent is late, so if they needed the file they could make a spreadsheet of their own if they wanted to.
(Just because I feel it is important to add: No, I am not heartless, I am not trying to put tenants out on the streets, but looking to give them a breakdown of their rent so they can apply for help paying their back rent due to the current pandemic affecting them, if that is what they need.)
Hello!
In the DATEVALUE function, the argument must be written as text. Add quotation marks.
datevalue("2021/06/08")
You can also calculate the difference between dates using the DATEDIF function.
I hope my advice will help you solve your task.
Thank you so much, That is Perfect! it works now. I appreciate you.
Hi,
I Has One Problem In Google Sheet.
Why My Google Sheet Only Cell G29 & G30 Cannot Be Change To "ddd"?
I'm Confuse.
Hi,
I want to mentioned 0 on saturday or sunday of every month on rest date & weekdays reflects 1. how to do this in execl,
Hi,
I have data in column A as below
Mon
Tue
Wed
Thu
Fri
Tue .... and so on depending upon the working day of a person in shift.
So practically speaking the working day of the next week is Tue and not Mon.
How do i post in Column B that start of week day for that person.
Thanks and regards
Govind
Hi, I've spend many weeks on trying to find a solution to calculate number of days per week. My Sheet has twelve columns for each month of the year. Below each month I have 5 columns for W1, W2, W3, W4 and Week 5. I need to know number of days for week1, meaning from 1 Jan till first Friday. Then Week2 till second Friday (will always be 5 days) and so on for all weeks, except last week will be from last Monday of the month till the end of the month. Currently I do this manually for all 52 weeks of the year by looking at calendar.
Hi ther, plz help me on this,
I have to do fortnightly payments (on Every alternate Friday) if i received a job card on monday or Thursday, that payment date( due date) should be reflect as alternative friday date on received date in excel payment sheet.
Please help me on this.