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")
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)
267 comments
I have this chart in my report, is it possible that if i put data daily in count daily colum and it automatically add in the the weekly and monthly colum and that weekly coloum become zero on every sunday.
Looking forward for your kind response.
Count-Daily Count-Weekly Count-Monthly
TRA/JHA 10 10 95
RADARS 26 26 384
RADARS-Closed 26 26 384
ICR Entries MGR 1 1 33
SOP/JHA Reviewed 10 10 95
BMS INSPECTION 4 4 47
PRE-TASK(TBT) 30 30 421
hi,
I need to make a table for the office lottery which needs to be paid every Wednesday for the year. I need a table that has employee names in the first column and the Wednesday dates for each month across the first row. How do I do this?
Hi Dee,
Try this formula =DATE(year,month,1+7*NthDay)-WEEKDAY(DATE(year,month,8-DayofWeek),2)
See this article for an example http://crispexcel.com/weekday-workday-how-where-to-use-these-excel-date-functions/
please help with formula. i want to count based on several criteria so i use countifs. the dates are entered as 1/2/17, 2/3/17, etc.
what is the formula to count the date if it falls on a Monday, or a Tuesday? this is what i have tried, which does not work:
if(weekday(A:A,1),2)
I'm trying to make a calendar to give my police officer son in law for Christmas. I've not given up but so far drawing blanks.
He works 3 days on and 3 days off. I could just mark the days he works on a calendar. The problem is sometimes they make adjustments to the days he works, not often but it does happen.
I need to be able to mark (for example) the last three days he worked. Then have it highlight his future work days based on that. Iow, high lite 3 days, skip 3 days but be able to modify the days and it auto adjust.
My brain feels like scrambled eggs :-( any ideas?
I need a formula to calculates the weekend dates only
HI,
I need a formula, where I put today's date in one cell, ie, 25/11/2016 and get exact date of after 15 months ie, 24/02.2018? Please help.
Thanks.
I need a formula, where I put one week in one cell, ie, January 2-6, 2017 and I have 5 other cells for Monday, Tuesday, Wednesday, Thursday, Friday, is there a forumla that I can input and it will automatically put 01/2/17, 01/03/17, etc.? Please help. Thanks.
If I have the number of days it takes for x amount of money to run out, how can I get the exact day to be calculated in a formula?
Thank you in advance.
hi guys, I have a question. I have a particular start date and a list of other dates.
I am required to extract 8 WEEKNUMS starting from my start date followed by the list of dates given. Again, i'm only required to extract 8 weeknum,
how to i do it?
help on this is much appreciated. Thanks all :)
How to list out every Thursday in between two dates? thank you in advance.
I have a timesheet which weekends are highlighted,I'd like the conditional formatting to highlight a day after, Monday, if holiday falls on a Sunday
Hope can get you help, thanks.
How to apply sum formula for week wise & week starts from monday & ends in sunday, is there any formula in excel which return the sum value week wise that to which automaticaly calculate the sum of Monday to Sunday in the perticular month.Please help in this.
Regards,
Gundu H Danolli
How can I get the 12 week prior Friday from the current date?
hi,
can you please help me.
how can i know which day it was 100 days ago.
is it correct: =today-100 ?
Hi Benjamininfo,
Yep. Just remember to add the parenthesis right after today:
=today()-100
thank you very much. :D
Hi
I am looking to add 39 calendar days from a date (i.e. incl. weekends).
But if the date falls on a Friday/ Saturday/ Sunday, i am looking to show the Thursday before.
Would anyone be able to help, please?
Thanks
Hi,
I am working for a project and for that project we need to calculate number of months between two dates in excel. For instance, I need to calculate the month between: 00-Jan-1964 and 00-Mar-1966. As you can see we don't have exact dates in them. Most of the excel functions that I found online work when I put exact dates in it. But we don't have any exact dates at our disposal. Any help would be greatly appreciated!
Hi,
Please i need you to help me use my spreadsheet to automatically do remaining number of days or countdown between two dates. e.g 02/feb/2016 to 20/June/2016 and for everyday it will automatically update the remaining days to get to 20/June/2016.
Thanks
Hi,
Good day.
I want to know what formula I can use to count the number of a day in a month (ie. the number of Mondays in June2016 or the number of Tuesdays in July2016, etc.) or is there such a formula for this?
Thanks! :)
need to calculate total income for the week.. need to get the total of mon to sat of the week
=IF(A2=WEEKDAY("Monday"),"Meade","Sanchez") Would you tell me how to fix this formula? I want the cell to say "Meade" for Monday, Wednesday, and Friday, and "Sanchez" for the Tue, Thur, Sat,(1st and 3rd "Sun") and 2nd Sun And 4th Sun "Meat" Thank you.
=IF(A2=WEEKDAY("Monday"),"Meade","Sanchez") Would you tell me how to fix this formula? I want the cell to say "Meade" for Monday, Wednesday, and Friday, and "Sanchez" for the Tue, Thur, Sat,Sund (1st and 3rd) and 2nd Sun And 4th Sun "Meat" Thank you.
Great article, thank you! I'm working on a pretty simple spreadsheet, but I've run into an issue with returning the weekday in a column. The formula below works great if there is data in column A. Is there a way to leave the weekday column blank if no date is entered in column A?
=TEXT(A2, "ddd")
I need to make 2 drop downs. One with selection of week 1 to week 8 and I want to limit the date in other drop down. Example if I select week 2 in drop down then other drop down should give me only option of dates starting 6th June to 10th June.
Please tell me if that's possible.
Hi,
I am a Marketing Executive. We have monthly tour plan. So I am trying to make up a formula to check for the current month and as per that the routes will be added to the corresponding dates and Sunday's has to be left blank. Format is
Month Date Route
Can you suggest how to use the formula for this
I am creating a protected 60-day calendar. Cell E2 is unlocked for a date input. The calendar week starts on Monday, going through Sunday (A7-G7). The calendar grid is A8-G8 all the way down to A17-G17. I want to be able to input a date in E2, (ie:5/10/16) and for excel to know that 5/10/16 is a Tuesday, so it automatically inserts 5/10 in the cell B8. I can then formulate for the autofil of the rest of the dates all the way through the end of the 60 days.
I just cant remember how I set this up before, where excel knew which day (monday-sunday) to start the calendar by entering a date in E2...
IF DUE DAY IS SUNDAY. HOW CAN I FIND -1 DAY SATURDAY.
I am trying to create a dynamically updated calendar based on a given date propagating to a 6 months individual calendar grids. I am trying to find out how to auto insert the correct first day of the month under the correct day. Example my project starts Feb 20 so the first calendar grid would show February 1st as the Monday so the second cell from the left of the calendar since my week starts on Sunday.
Hi, Can some one help me to separate time bands as per below through excel formula for each day and date
Weekday 1800-0000 hrs = Prime time
Weekend 0600-0000 hrs = Prime time
Weekday 0000-1700 hrs = Non Prime time
Weekend 0000-0600 hrs = Non Prime time
Hi, Can some one help me to separate time bands as per below through excel formula for each day and date
Weekday 1800-0000 hrs = Prime time
Weekend 0600-0000 hrs = Prime time
Weekday 0000-1700 hrs = Non Prime time
Weekend 0000-0600 hrs = Non Prime time
Week Day Hour PT:NPT
Wednesday 0 NPT
Wednesday 1 NPT
Thursday 2 NPT
Friday 3 NPT
Saturday 4 NPT
Sunday 5 NPT
Monday 6 NPT
Tuesday 7 NPT
Wednesday 8 NPT
Thursday 9 NPT
Friday 10 NPT
Saturday 11 PT
Sunday 12 PT
Monday 13 NPT
Tuesday 14 NPT
Wednesday 15 NPT
Thursday 16 NPT
Friday 17 NPT
Saturday 18 PT
Sunday 19 PT
Monday 20 PT
Tuesday 21 PT
Wednesday 22 PT
Thursday 23 PT
Friday 24 PT
Saturday 25 PT
Thanks in advance
I have scenario such as if in a column contain date weekend or holiday we should get the previous working day.for example Date:04/30/2016 is a Saturday,so I should get the Friday's date (04/29/2016)if this results is a holiday then we should get Thursday's date( 04/28/2016).
Please help me on this!
Hi experts, I'm looking for divine intervention!
I need to be able to enter a date that will count back 14 days and show the Monday ahead e.g. enter 18 February 2016 and get a return of 8 February 2016, however at the same time if I enter a date that is a Monday it will show the previous Monday only e.g. enter 15 February and get 8 February.
I tried using this formula but anytime i enter a date that falls on a Sunday it skips forward to the upcoming Monday...!!??!
=IF(WEEKDAY(D4)<2,D4+2-WEEKDAY(D4),D4+2-7-WEEKDAY(D4))
Make sense? Thanks everyone
I have a timesheet for our employee, weekends are highlighted so are US holidays based on countif formula, my dilemma is if a holiday falls on a Saturday I'd like the conditional formatting to highlight a day before instead and if the holiday falls on a Sunday, I'd like cf to highlight it on the time-sheet a day after, Monday. Please help, thanks,
Svetlana,
Thanks for your tutorial, I want to do finding next the four firday, but without public hoildaym, ie. tomorrow is chrismax hoilday and 7 days later is 1 Jan, so the next four firday would be 8/1, 15/1, 22/1 and 29/1.
How can i do it in execl? thanks a lot
Hello, Jesse,
Please enter the formulas below to 4 different cells:
=WORKDAY.INTL(A1,1,"1111011",$B$1:$B$3)
=WORKDAY.INTL(A1,2,"1111011",$B$1:$B$3)
=WORKDAY.INTL(A1,3,"1111011",$B$1:$B$3)
=WORKDAY.INTL(A1,4,"1111011",$B$1:$B$3)
So you'll find the 1st, 2nd, 3rd and 4th Friday. Please note that range $B$1:$B$3 should contain Public holidays.
Very Very Thanks
Svetlana - I need your expertise as I am truly struggling to find the formula for “next week 1st working day”. Currently I am using this formula =IF(A1="","",A1-WEEKDAY(A1)+2+IF(WEEKDAY(A1)>=2,7)) and it works great where every result returned on Monday.
However, the return date or that Monday is our Public Holidays so it is not going to be our 1st working day of the week. Is there a formula to include Public Holiday? I already have Public Holidays table set up.
Hello, Aziruzam,
The following formula should work for your task:
=WORKDAY.INTL(IF(A1="","",A1-WEEKDAY(A1)+1+IF(WEEKDAY(A1)>=2,7)),1,1,$B$1:$B$3)
Please note that range $B$1:$B$3 should contain Public Holidays.
If I have a Saturday date (12/5/2015) how do I figure out how to get a the Saturday date for 2016?
i want to insert starting day and end day and date in excel as follwos
start date 15.10.2015, end date 16.10.2015
ans. i want 16-16.10.2015
Hello Shiv,
To enter the date in the format 15.10.2015, select the cells, press Ctl+1, switch to Custom and type the following format in the "Type" box:
dd.mm.yyyy
Hi
I want to convert weekday name Mon, Tue, Wed to be converted to no of weekdaylike 1 for sunday, 2 for monday. Can you help me out
Hi Surjeet,
If you have a date displayed as the weekday name, you can use a usual WEEKDAY function like =WEEKDAY(A1)
If those are the text values, you can use the following nested IF's:
=IF(A1="Sun", 1, IF(A1="Mon", 2, IF(A1="Tue", 3, IF(A1="Wed", 4, IF(A1="Thu", 5, IF(A1="Fri", 6, IF(A1="Sat", 7, "")))))))
What if I would like to see the text WE + the month and date (in numeric) to my cell? Like WE 11/02.
Truly appreciate your help.
I want to give employee reviews on the 90th day. If the 90th day falls on a SAT or SUN I want to give the review on the previous FRI instead. How do I use =IF(WEEKDAY function to give the date and not just the text FRI?
For instance, the review date falls on 11/21/2015 a SAT (in Cell B5) but want to give revised review date of 11/20/2015 a FRI (in Cell C5) instead. Or if review falls on 11/22/2015 a SUN (in Cell B5) but want to give revised review date of 11/20/2015 a FRI.
I know I have to subtract 1 (SAT) or 2 (SUN) but when I put -1 or -2 it doesn't work. Please I have tried many different ways to no avail.
Thank you
Hi Svetlana
It would be a great help if you can tell me the syntax to use a date both as holidays and weekdays (As per situations).
For example: 1-April-2016 is a Holiday, but if task is done on 1-April-2016 (start date) then the date should be included else excluded.
Waiting for positive reply :)
Hi!
Thank you for the article! In a range of dates I need to know how many days there are in one month and how many days in the following month!
Example:
30/05/2015-13/06/2015 : 2 days in May and 12 days in June.
Is it possible? Thank you ;)
Hi
I want to calculate moving weekly average from the data column. The start date should Sunday ending on Saturday. So the average # should be same for that week and the same for the next week and so on. This is monitor weekly permit limit for a process plant data. I don't need rolling weekly average but moving fixed week average. Thanks.
Hi, I want the spreadsheet to calculate as of the start of the working week (Monday) each time I open it. I use the now function which gives an indication but my data would be more accurate if dated as of the start of week. Can you suggest formula for this please?
WE NEED TO MAKE A SCHEDULE IN THAT 2 PEOPLE WILL WORK EACH WEEKEND (TWO PEOPLE ON SATURDAY AND ONE PERSON ON SUNDAY ) THERE ARE 8 PEOPLE TOTAL TO WORK - HOW WOULD WE GENERATE A RANDOM ROTATION FOR SOMETHING LIKE THIS. IF IT IS NOT RANDOM THE SAME PEOPLE WILL WORK ALL HOLIDAY WEEKENDS
THANK YOU
Hi,
Great article, thank you. Not sure if I missed it - I need to count working dates between 2 dates and then working dates relating to the previous month. I can do it long way of cause, but maybe there is an easier way?
example:
25/07/15-28/08/15 -25 work days - 4 relates to July
Hi Namesake :)
You can use the NETWORKDAYS function, e.g. =NETWORKDAYS(A2,B2) where A2 is the start date and B2 is the end date.
Is it possible to only do certain dates and days of the week, say every Monday, Wednesday, Friday for a certain time frame? Thank you.
Hi Luvly,
It depends on exactly what you mean by "do certain dates". Can you clarify please?
=IF(A2=WEEKDAY("Monday"),"Meade","Sanchez") Would you tell me how to fix this formula? I want the cell to say "Meade" for Monday, Wednesday, and Friday, and "Sanchez" for the ether days. Thank you.
Hi!
You can use the following formula:
=IF(OR(WEEKDAY(A13)=2, WEEKDAY(A13)=4, WEEKDAY(A13)=7), "Meade","Sanchez")
If you are curious to learn more about the WEEKDAY function, please check out the following tutorial:
https://www.ablebits.com/office-addins-blog/excel-weekday-function/
I need to set up a chart with backdating from an event date. Some of those days have to be on a Tuesday. Is it possible to set a formula so that the dates returned are always on a Tuesday, regardless of the day of the week of the original event date? Or, do I have to set each event individually, rather than cut and paste?
Dependent on how your data is.
Conditional format; =weekday(date(year,month,day))=3
Anything in *s mean link it to relevant cell. I have numbers 1-31 in row 1 for example and months (Jan-15 format) then from B2 till the end of my table I have the conditional format on. The calendar is colour co-ordinated. The date formula only likes numbers, if you are going to link to the "Jan-15" then that part of the formula has to be; date(year(*cell*),month(*cell*),*day*)
Sunday is day 1, therefore Tuesday day 3
what if I want to calculate the number of days between two dates but only for weekdays (exclude weekend)? thanks.
Hi Saehu,
I have just published a new article that covers exactly this task. It explains how to count weekdays excluding weekends and, optionally, holidays:
https://www.ablebits.com/office-addins-blog/excel-workday-networkdays-functions/