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 2. Total comments: 159

  1. I have created a project schedule with conditional formatting to show week day and weekends - which works perfectly. Each task has a set duration (of workdays) before the final product. I currently have it formatted and coded so I input the due date and it plans backwards using the duration's set for each task.

    This however does not exclude weekends, how do I use excel to automatically update the number of days if a weekend falls in that space?

    IE if a task takes 5 days but day 3 and 4 are weekends the task would update to take 7 days? Is this even possible?

    My aim is to then use that data as a bar graph so the schedule automatically grows/shrinks based on the user inputted requested end date.

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

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

      1. Thank you Mr. Trifuntov!

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

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

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

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

          1. Sorry, your reply does not relate to my point.

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

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

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

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

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

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

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

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

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

  10. How do i formulate it to say yes if it is a weekday and no if it is a weekend?

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

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

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

      1. Thank you so much, That is Perfect! it works now. I appreciate you.

  13. Dear Sir,
    My doubt is quite a basic one. I have the Seven Days of the week. I want to now create a dummy for those days as

    Monday to Thursday = Weekday
    Friday to Sunday = Weekend

    How do I do that in Excel?

  14. Hello

    I want to identify last Thursday of each month from all Thursdays by a formula from given dates of style 04FEB2021,11FEB2021, and so on. Thanking you in advance.

    1. Hello!
      To find the last Thursday of the month use this formula

      =EOMONTH($A2,0)-MOD(EOMONTH($A2,0) +2,7)

      A2 - cell with date.

      To convert text to date, I recommend reading this article.

  15. Hello,

    I have a very complex tracking sheet for my work.

    One of the functions counts the amounts of any specified day within a date range.

    I would like to add one more part to this formula and am not sure how.

    Current formula:

    =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(Z2&":"&AA2)))=V2))

    Is there a way so that my formula will equal 9, (instead of 10) in cell AC when it is complete? The formula would need to use a list of populated holidays from another sheet titled “Lists”. That range of holidays is - F2:F5. When it calculates, it will determine that because the Day after thanksgiving is a Friday and falls within that range, it will automatically subtract an additional day.

    How can this be accomplished?

    Thank you in advance!

    1. I received your email and it works beautifully!!

      Thank you!! LIFE SAVER!!

  16. I have a production schedule on excel that the date column feeds off another cell. Basically todays date , plus the amount of time in the daily work schedule. Formula currently is = I3 +TODAY() So if today is 8/28 and I have 20 + jobs in lines I3,I4,........and so on. Each one adds up to .25 of a 8 hr shift. So my sheet shows 8/28 for the 1st 4 lines, on the 5th line it turns to 8/29 as it should as my 8 hr capacity is used up. My issue is I cant figure out how to take out Sat. and Sun and holidays from being populated as dates, therefore making it look like I can complete jobs sooner cause its using those 2 day we dont work on.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =WORKDAY(TODAY(),I3,E1:E6)

      For more information on how to add weekdays to a date, read here.

  17. I have a cell with a date that is changed each week in A1 and formula to give me the day of week starting with Sunday ending Saturday and also give the date of each day based on the date in cell A1. The following formula is used in each cell C5:I5 =DATE(YEAR($A$1),MONTH($A$1),DAY($A$1)) C5 is Sunday Then D5:I5 adjust the Day($A$1+1) it's +1 for Monday,+2 for Tuesday,+3 for Wednesday,+4 for Thursday,+5 for Friday,+6 Saturday. My problem is since June only has 30 days it skewed the DAYS of the week but the DATE is correct, so it reads Sunday 28th Monday 29th Tuesday 30th Monday 1st Thursday 2nd Wednesday 3rd Thursday 4th. If I change the formula starting on the first thru 4th to be =DATE(YEAR($A$1),MONTH($A$1+6),DAY(I5+1)) then it works until there is a change from 30 to 31 day month.

    Is there a formula that will recognize and make the adjustment automatically?

    1. Hello Cindy!
      I propose the formula = $ A $ 1 in cell C5, the formula = $ A $ 1 + 1 in cell D5, the formula = $ A $ 1 + 2 in cell E5, and so on. I think this will help solve the problem.

  18. Hi, please would you know the function or a way i can use to get the weekend (Saturday) date of a particular day in a week. Let's say i want to automatically get the weekend date of today (6/23/2020) in my cell. conditional formatting didn't work for me. Thanks in anticipation.

    1. Hello Michael!
      If I understand your task correctly, the following formula should work for you:

      =A1+(8-WEEKDAY(A1,16))

      In WEEKDAY function, use parameter 16
      I hope this will help

      1. Wooooooow! I wish could give a hug right now. It worked perfectly. You saved me a lot of stress. Thanks a lot, great work you're doing here!

  19. =OrText($B$3,"DDD") ="SAT",text($B$3,"DDD")="SUN" Hi.am trying to use this formula in conditional formating, to highlight Saturdays and sundays..can you please help me... there is something am missing... can you please help me

    1. Hello!
      The formula below will do the trick for you:

      =WEEKDAY(B3,2) > 5

      I hope my advice will help you solve your task.

      1. Thank you..i think there is something wrong with my excel...all the other formulas are working fine,, but only this weekend highlighting formula is not working for me.

  20. Hello
    Can you help me?
    I need to add 2 days if the date falls on a Saturday and if it falls on sundays, we add 1 day

    1. Hello!
      You need to determine the day of the week using the WEEKDAY function and apply it as a condition in the IF function

      =IF(WEEKDAY(A7,2)=7, A7+1, IF(WEEKDAY(A7,2)=6, A7+2,A7))

      Hope this is what you need.

      1. =OrText($B$3,"DDD") ="SAT",text($B$3,"DDD")="SUN" Hi.am trying to use this formula in conditional formating, to highlight Saturdays and sundays..can you please help me... there is something am missing... can you please help me

  21. Hi, I am having an issue and I do not understand why this is happening.
    I am using Google Spreadsheet.
    My year begins on a Sunday, December 29th (cell B1) and ends on Saturday, January 4th (cell B4)
    Since the formula WEEKNUM refers to the date in cell B1, and is a Sunday, I omitted the type since the week begins on a Sunday, per Excel reference sheet: -> System 1: The week containing January 1 is the first week of the year, and is numbered week 1.

    But whatever I do, it always returns as week number 53 instead of 1, as it should be.

    The second issue is that the second week begins on Sunday, January 5th (cell J1) and ends on Saturday, January 11th (cell P1). Since the formula still refers to the Sunday of the first day of this week (January 5th), It returns as week number 2.
    That would be correct week number is the first week wasn't considered as week #1.
    Because of this issue, Week 1 does not exist and causes problems with futur weeks and years.

    I would be very grateful if you could help me find a solution to my problem.
    Thank you very much and have a good day :)

    1. Sorry, I meant to say in my second sentence: "I am using Excel 2013, I got a bit distracted"

  22. Hi,
    I try to get a result for the following example: if today is 04/14/2020, and last month 03/14/2020 fell on the weekend, what formula does it apply to bring me the next working day in March = 03/16/2020?
    Thx

    1. Hello Cristian!
      If I understand your task correctly, maybe the following formula should work for you:

      =IF(WEEKDAY(EDATE(A1,-1))>5, WORKDAY(EDATE(A1,-1)-1,1), EDATE(A1,-1))

      Hope you’ll find this information helpful.

  23. In weeknum suggestions why we have to use different numbers for monday like 2,3(0-6),11 and for sunday 1,17. For ex: for sunday we can use 1 right, why 17 also there for sunday, and where will have to use 17, is there any logic for using 1 or 17, like here we have to use only 17 for sunday but not 1, please let me know this, thankyou

    1. Hello Kiwi!
      The list of possible values for the return_type argument in the WEEKDAY function is set by Microsoft. You simply choose a type from the list which suits better for your task. You can use any of the them as they just determine what day of the week to use as the first day.

  24. Hi, I am trying something a bit different than what ever everyone else seems to be doing. I hope you can help me as I've been trying different ways for a very long time now and am getting nowhere fast.
    I am building a weekly schedule and can allocate weekdays easily. I am trying to enter holidays into this formula but it does not work. I don't care about how many days it takes. I am only concerned with what my end date will be (without weekends or holidays).
    This is what I've been entering so far..Example (=weekday,1). "weekday" being in the box next to it. then I simply follow through with that formula across the row and it self tabulates the weekdays only. When I try to enter a holiday with it...example (=weekday,1, B6:B12) "these would be dates in a different box", it does nothing. hope this makes sense. Please help if you can.

    1. Hello Rob!
      If I understand your task correctly, the following formula should work for you:
      =IF(AND(WEEKDAY(A1,2)<6,ISNA(VLOOKUP(A1,$G$1:$G$7,1,FALSE))),"workday","holiday")
      where $G$1:$G$7 - list of holidays.

  25. I am using conditional formatting to highlight weekends. If the first falls on a weekend it does not highlight. I am using =OR(WEEKDAY($A1)=7,WEEKDAY($A1)=1). What is wrong?

    1. Hello Bill,
      Since 30 and 31 of December is the 53rd week of 2019 and 1st to 5th January is the first week of 2020, the formula for this particular week needs some modification. Please try the one below:

      =IF(OR(WEEKNUM($A1, 1) = 53,WEEKNUM($A1, 1) = 1), TRUE)

      Hope it'll work for you.

      1. Thanks for the tip, but it didn't quite work. I am using a new spreadsheet for the year. However, I did get it to work by starting it in the correct cell (not $A1 but $A2). I like your site, I have learned a lot from it.

  26. Hello, I've got a problem with a specific formula.

    I have 2 cells generating dates:
    Todays date (using =now () ) - In the format of dd-mm-yy hh:mm:ss
    Working date (using =workday(todays date,5)+time(15,0,0) so it adds +5 to working date + declares time of 15:00:00. For my purpose, I need the format to remain dd-mm-yyyy 15:00:00.

    The issue that I'm having is that once the time passes 15:30:00 on today's date (=now() function) - I need the working date to increase from 5 to 6, so it becomes tomorrow's work.

    Can you please advise on this?

    Kind Regards,
    James.

    1. I forgot to add, this will probably need to be converted to an IF function, my cell references are:

      Todays Date = I2 ( =NOW() )
      Working Date = I3 ( =WORKDAY(I2,5)+TIME(15,0,0) )

  27. If the date field is blank how do I get the return cell day of the week to stay blank? The =WEEKDAY(A2) formula is working fine and reporting as needed into the day of the week when the date is filled in, but if the date cell is blank yet (haven't added that data line yet) how do I get the day of the week cell to stay blank also?

    1. Try:

      =iferror(weekday(a2), " ")

  28. I have an excel sheet Where I need one column to display a date and another collum to display what date is 4 days away but only count business days. For example, Monday to display that same Friday in the next column, Tuesday to display the following Monday's date, Wednesday the next Tuesday so on and so on.

    1. CJ:
      I think what you want to use is the WORKDAY function.
      Where the first date is in A18 and you want a workday 4 days from the date in A18 enter this in B18:
      =WORKDAY(A18,4)
      So, if the date in A18 is 6/7/18 four workdays forward is displayed in B18 as 6/13/18.
      If you need to know the day of the week 6/13/18 is then this will show it in C18:
      =CHOOSE(WEEKDAY(B4),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
      You can enter "Sunday" or "SU" or Sunday in another language.

  29. I need a formula that will tell me if a certain date is the 1st, 2nd, 3rd, 4th day of the week.

    1. Tammy:
      There is a complete explanation of this topic here:
      https://support.office.com/en-us/article/WEEKDAY-function-60E44483-2ED1-439F-8BD0-E404C190949A
      Essentially you enter the date you're interested in and either accept Excel's default return type using Sunday(1) through Saturday(7) or enter the optional return type. It looks like this with the dat in A1:
      WEEKDAY(A1) with the default return type or WEEKDAY(A1,2)
      with return type 2. Return type 2 is Monday(1) through Sunday (7).

  30. If today is Friday so my value should be 30 otherwise value is 0. Date
    format is DD/MM/YYYY ( 01-May-1991).

    Example is below mention. I hope you give your response earliest.

    Date Results
    1-May-91 = if Friday = 30 other wise 0

    Note:- Friday is not mentioned in the data which i have require

    1. Mahendra:
      If you do not need to display the word "Friday" then this will work:
      =IF(WEEKDAY(A33)=6,30,0)
      Excel's normal setting is that Friday is 6.
      If you need to display the day's word it might be easiest to use a helper cell. In the helper cell you could enter:
      =CHOOSE(WEEKDAY(A33),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
      or a number of other variations on this technique all of which are explained here in AbleBits. Just search Weekday Function.

  31. There are 3 shifts, Every shift needs change duty after a Week (Sunday) of the month through 24:00 hrs. as given below detail.

    Date: Friday,01/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Saturday,02/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Sunday,03/12/2017
    Shift:A 00:00 TO 08:00 hrs (Night-duty)
    Shift:B 08:00 TO 16:00 hrs (Morning-duty)
    Shift:C 16:00 TO 24:00 hrs (Evening-duty)

    Date: Monday,04/12/2017 (Duty Shift would be Changed)
    Shift:A 16:00 TO 24:00 hrs (Evening-duty)
    Shift:B 00:00 TO 08:00 hrs (Night-duty)
    Shift:C 08:00 TO 16:00 hrs (Morning-duty)

    Please help me that how to set formula in excel that will show me Shifts: A, B or C will perform their duties on given date in 24:00 hrs.

    May kindly please be helped me in this case. in advance I shall be very thankful for him/her.

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  32. Hi Dear,
    pls solve my below problem how i can calculate number of days without holidays. Please send me formula urgently.

    Wednesday,November, 01, 2017 to Saturday,November, 18, 2017

    1. Hi Zahid,

      Please try to use one of the following formulas:

      1. =NETWORKDAYS(DATE(2017,11,1),DATE(2017,11,18),{"11/01/2017","11/02/2017"})
      This formula returns the number of working days between two dates, excluding weekends and any holidays specified in curly brackets.

      2. =NETWORKDAYS.INTL(DATE(2017,11,1),DATE(2017,11,18),1,{"11/01/2017","11/02/2017"})
      This formula does the same as the previous one. The difference is that you can adjust the calculation of weekends in the formula by changing the highlighted parameter if necessary.

      Hope this will work for you.

  33. if weekday of a date cell is falling on monday or Wednesday or Friday the cell should display a content or if the weekday of a date cell is falling on Tuesday Thursday Saturday I want to display another content.

    1. Hello,

      IN SHEET1 I have the falowing list.
      M W F T T S
      A1 = NAME 1 b2 = NAME 2
      A2 = TOPIC B3 = TOPIC 2
      A3 = 25-AUG-17 B4 = 13-SEP-17
      A4 = 20-DEC-17 B5 = 28-OCT-17
      A5 = NO.STU B6 = NO.STU

      In SHEET2 I have heading from 1-Jul-17 - 31-Dec-17 in each cell.

      as per date in a3 to a4 a i want to display the content available :
      C2 = SHEET1!A1 IF date in c1 falls in b/w 25-aug to 20 dec 17
      C3 = SHEET1!A2 IF date in c1 falls in b/w 25-aug to 20 dec 17
      C4 = SHEET1!A5 IF date in c1 falls in b/w 25-aug to 20 dec 17

      D2 = SHEET1!B1 IF date in c1 falls in b/w 25-aug to 20 dec 17
      D3 = SHEET1!B2 IF date in c1 falls in b/w 25-aug to 20 dec 17
      D4 = SHEET1!B5 IF date in c1 falls in b/w 25-aug to 20 dec 17

      I want a single formula to be applied for all cell in a row.

      PLEASE REPLAY

    2. What can be the formula for this

  34. Hi,
    Please help me out to below question.

    If you have two id that condition is Eligible otherwise not eligible

    Voter ID Pan No. Aadhar No. Result
    45786 8022331 Eligible
    321321RD Not Eligible
    151165R 888211221 Eligible
    8825645 Not Eligible

  35. We have set meeting dates throughout the year (weekdays only), notice must be given 10 days before and then 3 days before. The notice needs to be given on a weekday. I am very new to formulas and find this a little over my head. Are you able to give me a formula or a step by step?

    1. Hello, AJ,

      would you please specify what notice you want to see? Do you want a specifically coloured cell or something else? How is your data stored, what columns do you have? Please, give us more details on your task so we could help you better.

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

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

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

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

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

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

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

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

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

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

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

  47. what if I want to calculate the number of days between two dates but only for weekdays (exclude weekend)? thanks.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)