Hi,
I am hoping someone might be able to help. I need to create a list of dates that increases by increments of one day but excludes certain dates in an array Labeled "Holiday".
I know this can be done using Networkdays but I want to include weekends and only exclude the dates in the array.
Any help would be greatly appreciated.
Cheers,
Guy
maybe
=IF(ISNA(MATCH(A1+1,RangeHolidays,0)),A1+1,A1+2)
copy down.
where RangeHolidays is a named range of cells that lists the holiday dates, and holidays are only single dates, not several consecutive dates.
Last edited by teylyn; 09-22-2010 at 05:34 AM.
this formula caters for situations where two consecutive days can be holidays, like Dec-25 and Dec-26 in countries that observe Christian holidays.
=IF(ISNA(MATCH(A1+1,$E$1:$E$5,0)),A1+1,IF(ISNA(MATCH(A1+2,$E$1:$E$5,0)),A1+2,A1+3))
The principle can be expanded to include up to 6 consecutive holidays given the maximum of seven nested IFs in Excel 2003.
Brilliant that works, thank you very much for your help.
Guy
You could cope with up to 4 consecutive holidays with this version
=A1+MIN(IF(COUNTIF(holidays,A1+{1,2,3,4,5}),"",{1,2,3,4,5}))
Audere est facere
I'm expecting daddylonglegs to come up with something more efficient.
q.e.d.
Thanks, DLL!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks