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
I'm expecting daddylonglegs to come up with something more efficient.
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
q.e.d.
Thanks, DLL!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks