Forum Discussion

175429's avatar
175429
Copper Contributor
Jul 08, 2025
Solved

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, sta...
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jul 08, 2025

    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

     

HansVogelaar's avatar
HansVogelaar
MVP
Jul 08, 2025

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.

175429's avatar
175429
Copper Contributor
Jul 08, 2025

what goes in between the quotation marks?

Resources