I tried to do a search for an answer to my question but was unable to find one, so here we go...
I need to count the number of days between 2 dates but exclude Holidays (I specify in a list). I don't want workdays, I need calendar days.
The number of days between the two dates I have (that is easy), I am having trouble with excluding the list of dates when the dates fall within the range I am subtracting from one another.
If you have start date in A2, emd date in B2 and holidays in H2:H10 then try this formula to get the number of non-holiday dates (including both A2 and B2)
=SUMPRODUCT(ISNA(MATCH(ROW(INDIRECT(A2&":"&B2)),H$2:H$10,0))+0)
Audere est facere
Sorry, already answered
Tried it and the formula is only subtracting one day from the calculated total.
I put 1/1/12 into A2 and 1/1/13 into B2 with 6 dates in the H column range and I get a total of 366. Without the dates in the H column I get 367.
OK, silly question....are all the 6 holiday dates in 2012? Are you sure they are valid dates?
See my example attached
Turns out if is not a silly question...my mistake they all feel outside the date range being calculated...I feel rather silly, my apologies. The formula works flawlessly...once I put the correct information into the correct fields, thank you
.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks