Comments on: Excel WEEKDAY function: get day of week, weekends and workdays

There are a variety of functions to work with days 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. Continue reading →

Comments page 7. Total comments: 284

  1. Earlier you acknowledged:
    "Note. Though the WEEKDAY function is available in all Excel versions, from Excel 2013 to 2000, the return_type values 11 through 17 were introduced in Excel 2010 only, therefore they cannot be used in earlier versions."

    So what are the differences between the single digit and two digit Return_types? For example, 2 appears to do the same thing as 11.

    1. Hi Dave,

      Absolutely so, 2 does the same thing as 11. The difference is that 11 can be used only in Excel 2010 and higher versions, while 2 works in all versions of Excel 2000 to 2016.

  2. Hi,

    j juts need automatic date count, example if i type 1 in other cell have to show 1 days, if i type 2 it has to count 2 days,

    can you help me out please

  3. Hi,

    I have something to ask about the dates, because i want to make an automatic Present and Holiday for the days of Monday to Thursdays = Present and for Friday is = Holiday please help me

  4. Hi,
    If I have a number let's say 11, how can I get number of weeks like 2 week, 2 days
    instead of 2.2

    Please help

  5. For my previous question, I got this syntax: =F2-DATE(YEAR(F2);1;0; however, it only extracts all the date numbers in a year. i.e. 1,2,3,...364,365,1,2,3,...365,366,1,2,3,.... what I wanted was for a sytax that would extract the list for each days of a year recurring every month.
    Thanks for your support

  6. Hello,

    Thanks for the informative article. I was intereted to know how to get the list of dates of a year in each month but only till the end of each month. Eg. 1,2,3...29,30,1,2,3,...27,28,1,2,3,...29,30,31,1,2,3...
    Thanks

  7. Hi,

    I stuck with a formula here. I have A2 (Work Start Date) B2 (Traget Completion Date) and B3 (Actual completion Date)and delays in B4. I want to retun the following logical formula. Can you please help.

    So,B4 = If B3 is blank return TODAY - B2.

    Thank you.
    Regards
    SPD

  8. Hi,

    I would like to ask how can I calculate the number of days in this scenario

    Item Qty Date In (start) Date Out No. of Days No. of Stocks
    A 1 2-2-2017 1
    B 1 2-2-2017 2-14-2017 0

    How can i make a conditions that will show if there is no date out they will count the days base on the date today.

    Hope you can help me.

    Thank you.

    nina

  9. Hi
    Making a capacity grid based on two things weekdays and the day.
    For instance at the top I have Week 1 with a drop down going to Week 13 Right underneath i want to have the corresponding dates according to the week and month. Example. February week 2- 2/13-2/17. I want to be able to change the week and the dates change with it automatically. I didn't put the month in but I guess that's important huh???
    Any advice would be great

  10. Hello, I would like to highlight a cell as a marker for today and put word "Today" as an indicator for today in highlighted cell A1. When today is over, I would like that the word "Today" that already was in cell A1 moves to cell A2 with the same highlight, and it does not show in cell A1 because it belonged to yesterday's indicator.
    What is the formula? Thanks

  11. Is there a a formula that if i put a date it will give me what the date will be in 12 weeks time for example

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

  13. 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?

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

  15. 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?

  16. I need a formula to calculates the weekend dates only

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

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

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

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

  21. How to list out every Thursday in between two dates? thank you in advance.

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

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

  24. How can I get the 12 week prior Friday from the current date?

  25. hi,
    can you please help me.
    how can i know which day it was 100 days ago.
    is it correct: =today-100 ?

    1. Hi Benjamininfo,

      Yep. Just remember to add the parenthesis right after today:
      =today()-100

      1. thank you very much. :D

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

  27. 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!

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

  29. 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! :)

  30. need to calculate total income for the week.. need to get the total of mon to sat of the week

  31. =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.

  32. =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.

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

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

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

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

  37. IF DUE DAY IS SUNDAY. HOW CAN I FIND -1 DAY SATURDAY.

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

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

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

  41. 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!

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

  43. 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,

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

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

  45. Very Very Thanks

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

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

  47. If I have a Saturday date (12/5/2015) how do I figure out how to get a the Saturday date for 2016?

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

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

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

    1. 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, "")))))))

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

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