Forum Discussion
Calculate Due Dates, excluding Holidays and Weekends
Hello all!
I would like to calculate a due date, excluding holidays and weekends.
This is my data:
The "COH Due" column is being manually entered, but I want it to be automatic.
So, starting from whatever day is in the "Date Accepted" column, I want to add 7 business days to it.
I'm not sure what formula I would need for that.
Any help would be greatly appreciated.
Thank you!
The formula is WORKDAY( [start date], [number of days], [holidays] ) this assumes M-F are workdays
I am also attaching a handy table of holidays I created. You just need to add or delete the 'x' above the holiday to include or exclude that holiday from the list of holidays:
so with that you can use
=WORKDAY( A1, 7, Holidays)
EDIT - just saw your f/u note and that is because Excel is not counting the start day. If start on Day 1 and you add 7 days you don't get day 7 you get day 8. So you have a few options:
simply add 6 workdays instead of 7. this will work if you are always starting on a workday. If your start date is a weekend or holiday then it will be off
alternatively you could
=WORKDAYS( A1,7, Holidays) -NETWORKDAYS(A1, A1, Holidays)and that should account for those weekends and holidays
7 Replies
The formula is WORKDAY( [start date], [number of days], [holidays] ) this assumes M-F are workdays
I am also attaching a handy table of holidays I created. You just need to add or delete the 'x' above the holiday to include or exclude that holiday from the list of holidays:
so with that you can use
=WORKDAY( A1, 7, Holidays)
EDIT - just saw your f/u note and that is because Excel is not counting the start day. If start on Day 1 and you add 7 days you don't get day 7 you get day 8. So you have a few options:
simply add 6 workdays instead of 7. this will work if you are always starting on a workday. If your start date is a weekend or holiday then it will be off
alternatively you could
=WORKDAYS( A1,7, Holidays) -NETWORKDAYS(A1, A1, Holidays)and that should account for those weekends and holidays
I tried the workday function.
P3:P13 are my holidays.
But excel is giving me the wrong date. The date should be 7/15, but it is giving me 7/16.
Create a range of public holidays.
Select this range.
Click in the name/address box on the left hand side of the formula bar.
Type Holidays then press Enter. You have named the range of public holidays.
Let's say you have Date Accepted in A2 and down.
In B2:
=IF(A2="", "", WORKDAY.INTL(A2, 7, , Holidays))
Fill down.
what goes in between the quotation marks?
I forgot to add, the formula I've tried is =workday(H4,7) and it's not giving me the correct due date.
H4 = first date in the "Date accepted" column.
Excel is returning the date 7/16/2025 instead of 7/15/2025, which would be 8 business days, instead of the needed 7.
Which days of the week are business days and which ones are weekend dates in your region?
Hey Hans,
Thank you for your assistance. Another gave me the answer:
=WORKDAYS( A1,7, Holidays) -NETWORKDAYS(A1, A1, Holidays)
