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

  1. I have a spread sheet where i have a cell for sum of time worked for Monday through to Thursday(N,18) with an adjacent cell(O,18) for Friday. I also have a date cell(I,1) using the =TODAY() with adjacent cell(O,1) showing the day (=TEXT(I1,"dddd"). I want to hide the value of N18 when (O,1) = Friday and hide the value of (O,18) when (O,1) when (O,1) = Monday-Thursday. I can't seem to get the right start on this.

  2. I need help I have a formula returning a date from another date but I only want it to return weekdays.
    Columns are
    Release date Training date
    3/15/2018 =DATE(YEAR(A2), MONTH(A2), DAY(A2)+2)

    It calculates 03/17/2018 in the field but that is a weekend.

  3. Hi,

    I need:
    to establish the average time taken between start and end dates/times across multiple rows.

    The start & end data provided is presented in two separate columns 'DD/MM/YYYY HH:MM' and I need the difference per row then an average of the total rows.

    I have tried:
    =INT(end-start)&" days "&TEXT(end-start,"h"" hrs ""m"" mins """) to extract the day/hour/minute difference between start and end per row but cannot find a formula that will allow me to work out the average based on these results.

    Result will need to be specific to the minute.

    Please can you suggest/advise.

    Many thanks
    Alex

  4. Hi,

    Is it possible to compute a cell if date is weekday and not holiday or weekend? and compute a cell if date is weekend or holiday only?

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

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

  7. how to calculate week day for example present=23,leave=8,night shift=2
    week of=4 then salary-19000 ,so how can calculate salary and how to reduce week day

    1. Hello, Vinitha,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

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

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

  10. please advice.
    I'm creating a column that the result of dates come up on Tuesdays (on or before only) from another cell.

    (cell A2)
    Monday, August 28, 2017

    (cell B2) my formula:
    =IF(A2="","",A2-WEEKDAY(A2)+3)

    (cell B2) result:
    Tuesday, August 29, 2017 (I want this to be-
    Tuesday, August 22, 2017)

    but on another date like-

    Thursday, September 21, 2017

    (using the same formula as above, the result is correct or the way I prefer it which is-

    Tuesday, September 19, 2017

    I'll greatly appreciate your help.

  11. Please advise, I need to calculate the payment day for a vendor that has terms of 45 days , excluding weekends but and this is the but, if payment is set a Monday move to Tuesday . Is just one vendor with this criteria.

    Thank you

  12. please help me.
    if i have only a one date and when the date is complete numbers is increase day by day when the date is increase and highlight the cell in excel.

  13. Please can you help me with a formula in excel that will show me if the date I enter in a cell is weekend. Specifically, I need Date error message to be return if date is sat or Sunday. Thanks

  14. THANK YOU, I NEEDED THIS.

  15. Can you please help me with my problem? I have a listing of 2016 (ie) daily LIBOR rates. I need to figure out the LIBOR rate 2 business days before the 15th (i.e) of each month. Is there a way to set up a conditional formatting or a formula for this? Appreciate any help. Thanks in advance.

  16. For many years I have recorded the date on which my roses bloom each year. The date is in the format dd/mm/year. I now want a formula that runs through these dates and identifies the earliest date and the latest date on which the roses flowered. My current formula always returns, say, 01/06/2012 as earlier than 01/05/2015. I want to be able to ignore the year and find a formula that in my example records 01/05 as earlier than 01/06. In other words, when searching for the earliest or latest I want to ignore the year and just focus on the days and months. Any ideas?

  17. I need to set up a table with backdating from an event date. And in that if any of those dates are Sat/Sunday, it should automatically change it to Friday. Is it possible to set a formula so that the dates returned are always on a Friday for Saturday and Sunday.

    For Eg: 20th May and 21st May is the weekend. So if i enter that day, it should automatically change to 19th May(Friday).

  18. Today i went for an interview.the interviewer asked to me suppose the employee joins today. after 3 month i want to find out the employee joining date.which method is used in excel.please tell me actually i didnot answer the question.

  19. What formula can I use to pull next weekday to order from a list 246 (day of wk) for example sunday is 1 for us. So I want to get next order date from my list. Then I want to add a formula to get delivery date if it is 10 days add if holidays in between. Last there is a third factor some times we don't order cycle we order every 14,21 or 28 dat which we would add to delivery. Tired counting which day do we order next and adding delivery led time if we can accomplish in formulas.
    Thanks for the help, going in circles to figure it out.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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