holidays

1. 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...
2. 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...
3. 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...
4. 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...
5. 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...
6. 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...
7. 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...
8. 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...
9. 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.
10. 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...
11. 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...

Hi Everyone, I will be great-full if someone can help me with the preferably macro or formula on this. We have tasks assigned to each department with the due date. The tasks have been distributed in the order that they need to be completed. This is done so that each department completes the...
13. Pointing to files that don't exist

This VLOOKUP works fine. =IFERROR(VLOOKUP(C3,'R:\Patient Results\[050119.xlsx]Bob'!\$E\$2:\$F\$569,2),"") But I need the results to be blank if there's no file with that name or if the file exists but there's no tab within the file with that name. If I could get these 2 problems worked out it...
14. 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
15. Reverse date work days calculation

I currently have a calculation that takes the start date, adds the duration of days and calculates the finish date excluding holidays and weekends. I would like it to do the opposite. Take the finish date, add the duration and back into the start date. excluding Holidays and weeds Any help would...
16. Exclude certain dates fron holidays in the NETWORKDAYS function

I've set up a worksheet to calculate holidays. There is a table consisting two columns. On column with dates, which include and days-off (holiday, public holidays and business trips) and another column with types of holiday ("holiday", "Public Holiday" and "business trip"). I use this table for...
17. Display Workdays ONLY

Hi there, The code below displays / outputs a list all dates between a specified starting and ending date input by the user. However, I'm having a bit of trouble only listing WORKDAYS (Monday through Friday). An added BONUS would be to also eliminate Holidays from the list. Any advice or...
18. NETWORKDAYS formula

I have been trying to get NETWORKDAYS formula to work but for some reason it just won't do it. I have resorted to using the below formula but it just doesn't seem to want to take into account the holidays. so the formula is: IF(WEEKDAY(A3,2)>5,"",NETWORKDAYS(A\$2,A3,\$R\$3:\$R\$10)) A3 is the...
19. Conditional format based on named range dates

Hi All I have a named range 'Holidays' from BA2 to BA91, I need to look into the worksheet and highlight any dates that are equal to the named range 'Holidays' dates. I have tried to use Cell Value equal to ='Holidays' or without quote =Holidays but it didn't work. Please could anyone help...
20. Vlookup and IF Statement

I'm trying to automate a spreadsheet that calculates pay rates based on weekend weekday and public holidays. I have column A as the date starting 01/01/2019 Column B has the formula =TEXT(A1,"dddd") to add the day. Column C is a Vlookup to a Public Holidays table using the formula...

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

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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