CODEWITHSUNDEEP
excelexcel-formula
Antonio
Antonio

Reputation: 379

Difference between two date in excel excluding only holidays

Im looking if there is a formula to calculate data difference in excel considering weekend but excluding holidays: For example I want to know how many days are from 1st of Jan 2017 to 15th of Jan 2017 giving a range of Holiday (i.e 7 of Jan and 08 of Jan)

Upvotes: 0

Views: 1678

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Use NETWORKDAYS.INTL.

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The optional weekend can be either a weekend number, or 7 character string of 0's and 1's showing which day the weekend is on. 1 = non-workday, 0=workday.

So with 1st Jan in A1, 15th Jan in A2, 7th Jan in F1, 8th Jan in F2:
=NETWORKDAYS.INTL(A1,A2,"0000000",F1:F2)

Upvotes: 3

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Please try this,

     =TEXT((B1-A1+1-NETWORKDAYS(A1,B1))+NETWORKDAYS(A1,B1,A3:A4),"d")

where,
A1 = start date,
B1 = end date,
A3 - A4 = list of holidays in between which can be any range

Upvotes: 0

Srijan
Srijan

Reputation: 152

Try this:

=E6-E5-COUNT(H1:H3)

E6 contains end date E5 contains start date H1:H3 contains lsit of holidays

Upvotes: 0

Related Questions