1. A

    Resource Planning

    Hi, I've got a resource plan spreadsheet and its functioning fine, but i was wondering if anyone can suggest how i can get it to exclude weekends and holidays. my forumula is: =SUMIFS(Table1[[Hours/Day]:[Hours/Day]],Table1[[Resource Name]:[Resource Name]],Report!$B17,Table1[[Start Date]:[Start...
  2. B

    Date Subtraction using business days

    Hello, How would I go about subtracting two dates in the format below excluding weekends? Column A 07/22/2019 Column B 07/19/2019 Column C is the difference (Column A - Column B) would calculate a 3, but that is including weekends.
  3. A

    Excluding weekends and holidays from the result of formula =IF(BC456="OPEN",TODAY()-Z456,AU456-Z456)

    Hi all. seeking for your help. I am using =IF(BC456="OPEN",TODAY()-Z456,AU456-Z456) to get the number of days a case is open to date. However the result is in calendar days. Is there any way to exclude the weekends and holidays from the result of the said formula? Thanks Annie
  4. F

    Counting number of weekend days per month in a date range

    I am running into an issue trying to count by employee and by month, the number of weekends in time away requests that span multiple months. In one sheet "EmployeeList", I have a column with a list of all of my employees and have columns for each month of this year and next (Jan 2019 through...
  5. A

    How to put an ISBLANK in WEEKDAY

    Hi, I’m creating a calculator which will show an expectation each day someone is at work. Currently this is what I have, so on a weekday it returns 5, weekends return 8. However I would like it to return an empty cell if C3 is empty as this means they are not in. =IF(WEEKDAY(A3,2)>5,8,5) How...
  6. J

    Count weekends in a row

    Hey guys! I have a table with this format: <tbody> ID Date Day Value 1 31.12.2017 So 7 1 01.01.2018 Mo 8 1 06.01.2018 Sa 9 1 13.01.2018 Sa 8 1 14.01.2018 So 8 1 15.01.2018 Mo 7 2 01.01.2018 Mo 4 </tbody> Now I have this aim: I have a table that lists all IDs. Now I want to...
  7. A

    If and Weekday

    I am working on a chart that gives the user the option to include or exclude weekends in the forecasting of a project. Have not been able to write the formula correctly for the option to include the weekends. The design is simple: User places a "x" in a cell on worksheet #1 , if they want...
  8. U

    Conditional Formatting to identify Weekends and Holidays

    Hello, I'm looking for a formula to color cells based on weekends and holidays. In cells L2:AP2 are the days of the month. Using the conditional formatting rules I used the following formula "=WEEKDAY(L$2:AP$2,2)>5" to determine the cells to be formatted. In addition to weekends I would like to...
  9. U

    Creating a Macro for deleting entries falling on Weekends

    I need to create a macro for deleting entries falling on Weekends. I have a conditional formatting formula that colors the cells falling on Weekends "=WEEKDAY(L2:AP2,2)>5. So, I would need the macro to be able to delete entries from coloumn 3 to 799 if L2:AP2 is a Weekend day. Thank you in...
  10. A

    Removing weekends on column chart

    Hi all, New here and noobie to Excel. I'm in the process of creating a PNL chart for personal uses but came up to a problem that Excel kept showing weekend dates on my chart. I've searched around and people say to change the X-Axis type to "Text" but that don't help much because the cells I'm...
  11. R

    Help with Date Calculation

    Hello, I am trying to calculate completion dates for process steps. The steps have standard times in days associated with them, however, many of the steps take less than a full day to complete i.e. 0.25, 0.75 days. I tried using the WORKDAY function to calculate the completion date(s), however...
  12. R

    Date Calculation Assistance

    Hello, I am trying to calculate completion dates for process steps using the WORKDAY function, however, the WORKDAY function alone does not recognize partial days i.e. 0.25, 0.75, etc. Is anyone able to recommend a function that calculates completion dates for each step in a process? I will...
  13. M

    Conditional Formatting

    Hi, I have set up a conditional format in cell G6 that turns the cell red if the date in the cell is greater than 20 day from that date. but I want it to only count working days Mon-Fri and not weekends ie Referral date (cell G6) 16 Jan 18 the cell turns red after 20 days being 5 Feb 18, but...
  14. K

    Calculate the difference between two time sets and get the results in minutes and also consider the weekends?

    Hi, Do we have formula which counts the difference between two time sets and the results should be in minutes, it also should consider the weekends? To give you more perspective, we run a 24X5 support center and need to count the mins taken by the associate to respond to a customers query...
  15. A

    formula to calculate the date and time within the business hours by Adding hours and excluding Holidays weekends

    formula to calculate the date and time within the business hours by Adding hours and excluding Holidays weekends Any Help
  16. N

    Calculate no of days between dates, including weekends, excluding Holidays

    I need to calculate the number of days between two dates, including weekends but excluding holidays. I have a table listing the holiday dates named 'holidays'. Thanks
  17. N

    Highlight Holidays in excel table

    Hi, I have 31 columns, each representing a day of the month. Below I have 5 rows or so. I need to highlight all columns that are Holidays in the UK calendar. I have managed to get the sheet to highlight weekends, but really struggling to do the same for uk holidays. I have created a...
  18. S

    how to calculate difference in days between two dates that also contain time AND exlcuding weekends

    Hi all, I have two cells which contains the date & time stamp in the following format: "mm/dd/yyyy hh:mm" - A2 contains the "create date" and B2 contains the "review date". What I want to do is see how many days have elapsed between the create & review date. I found a formula which neatly...
  19. S

    Excel Formula for Every other Every third weekend

    Hi All, I was wondering if anyone has a formula to calculate the dates for a every other every weekend schedule for the year 2018, 2019. As an example - the month of January, the working weekends would be January 6,7 and January 20,21 then off for 2 weekends and back on for Feb 10,11 and 24,25...
  20. J

    Monthly workdays in a given month

    Hello: I am trying to create a weekday number by month excluding weekends and holidays. I created a table with a helper column with weekdays, weekends and holidays. Please take a look at column H of the attached file so you can see the logic: Example 1/1/2018 = Holiday 1/2/2018 WD 1 Jan 2018...

Some videos you may like

This Week's Hot Topics