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 9. Total comments: 284

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

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

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

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

  5. Very Very Thanks

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

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

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

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

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

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

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

  13. 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 ;)

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

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

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

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

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

    1. Hi Luvly,

      It depends on exactly what you mean by "do certain dates". Can you clarify please?

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

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

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

  21. what if I want to calculate the number of days between two dates but only for weekdays (exclude weekend)? 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 :)