1. J


    MARKETCAL2 will generate the days the market is closed for the year requested or the current year if that option is omitted, and is designed to work in conjunction with the MKTDAYS Lambda function to build an array of dates the market is open. It will generate a two column array with the name of...
  2. B

    NETWORKDAYS function with holidays and vacation days list

    Hi guys, Already tried search, but could not find an answer to my question. Is there a way to have the function NETWORKDAYS to use two list, one of holidays and another one of vacation days? I could not find an answer to this question, so, I think I will have to use only one list of "holidays...
  3. J


    MKTDAYS is the second of two posts intended to alleviate the problem with the STOCKHISTORY function where it returns an error when attempting to provide information for an invalid date as reported by @DRSteele . The first post was MARKETCAL which can be used to provide the required array of...
  4. R

    Expand array of dates based on 2 criteria (VBA)

    Hi I have a counter with a minimum value of 17. As long as this value is not reached, then extra dates should be added to an array of dates. The extra dates should be workdays: if for example 2 extra dates are needed to reach the 17 value, but the 24th of December is not a workday, yet 23rd and...
  5. R

    Dynamic holiday range formula

    Hi, I have the following formula which is inserted on sheets through VBA code: =SUMPRODUCT((B3:AF3="AD")*(WEEKDAY(B2:AF2,2)<6)*(ISNA(MATCH(B2:AF2,HolidaySheet!A2:A3,0)))) The A2:A3 range contains two holiday dates which are on a separate worksheet. The holidays change depending on the year...
  6. R

    One formula to count all ADs on workdays in a month

    Hi, I am looking for one Excel formula to put in cell A1 that counts all AD values on workdays (not weekends nor holiday 2-01) from the beginning to the end of the month. The dates (see image) are in row 2, the values in row 3. I am struggling with this.
  7. R

    Color cells if workday and holiday (VBA)

    Hello, I got a macro button which adds month sheets for a specific year that is entered in an inputbox. The following code unsuccessfully attempts to fill all working days with a pink colour if they match a range of cells that represent the holidays (and include blank cells). Is there a way to...
  8. R

    Excel double condition formula

    Hi, I am looking for an Excel non-VBA function formula based on two parameters. If a cell holds the value 10, then a date starting from 24 December is added, but only if this date is a workday. If this date is not a workday, then it takes the first day before this date that is a workday. If...
  9. N

    Finding holidays per country and date in holidays sheet and it appears in a weekly agenda worksheet

    Hello! I'm working with two worksheets. One worksheet called "Holidays" which has a table where the rows are holidays dates from multiple countries and in the columns the name of the countries. If on a date it's a holiday in one or more countries than below that country it appears the word...
  10. A

    Networkdays by quarter and holidays

    Hi everyone, I have calculated the total number of workdays between two dates by quarter using the formula below: =MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,2)))) Example: Start Date End Date 1/1/2019 4/1/2019 7/1/2019 10/1/2019 1/1/2019 4/30/2019 64 22 0 0 1/1/2019 12/31/2019...
  11. E

    Dynamic task calendar that accounts for holidays

    I'm developing a dynamic calendar (not shown) that sources its tasks from a task schedule (see enclosed screen capture). In the task schedule, the user enters the decision date in cell D1 and the task schedule populates the decision date in B13. The other dates are workdays relative to the...
  12. M

    Networkdays + Datevalue

    Hello, In Networkdays formula, we have "holidays" part, where we can easily use excel list of holidays that we created in advance. But what I want to do is to note holidays in the formula manually (not from existing list). For this reason I write holidays in the Networkdays formula with the...
  13. masud8956

    Listing holidays separately and finding values in aggregate

    Hello experts! I don't know if it is too much to ask. I have a situation like the following: A2:A10 shows the timeline of a project. C2:C10 lists all working days between A2 and A10 excluding weekends (marked in red)and public holidays. C2=A2 but for C3 I have used...
  14. 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...
  15. H

    Highlight all Mondays (or Tuesdays...) that start a week

    Hi. I am using CF to highlight all Mondays in a week using this formula =WEEKDAY(P5)=2 When I populated the dates from left to right across my columns, I filled right with this formula: =WORKDAY(S5,1,Holidays) However, since I've got my Holidays range to ensure that bank holidays aren't...
  16. K

    VBA for Networkdays

    Hi Everyone. I'm still busy with a workbook for our weekly timesheets. I have created a UserForm for the user to fill in everyday. This information will then be sent to the relevant day in the spreadsheet. On top of the UserForm the day of the week as well as the date is displayed. I got the...
  17. I

    Excel Date Formula - Production Log

    I am finalizing a production log that I have built...I have a Entered Training Date and a Graduation Date listed. I am trying to calculate the number of days instructed within the current month. I have the formula spelled out that gets me exactly what I was looking for, however, this formula...
  18. C

    Count Public Holidays that falls on a Saturday

    I have a list of public holidays and I would like to know how I could count the ones that fall on a satuday.
  19. A

    Using Workday function to exclude Holidays and Fridays

    I am using this formula in a spreadsheet =WORKDAY(M9+-14,-1,Exclude_HolidaysAndFridays) "HolidaysandFridays" are lists of company holidays, and Fridays. I was wondering if there was a way of incorporating the weekday function in this formula so as to eliminate having to make the Friday List...
  20. R

    Elapsed Time (h):mm:ss

    Hello, new to the forum with little knowledge of Excel. I've been searching for a formula for elapsed time showing in (h):mm:ss between to date/time Stamps i.e. Start date/time 7/10/2019 10:53 AM and finish date/time 7/10/2019 2:20 PM that will exclude weekends & holidays to no avail. I have the...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back