Hello,
Many thanks to those who helped point me in the right direction.
I thought I should post my solution here in case this helps any others. To recap: I need to indicate where more than/equal to/less than 5 days occur between 2 dates allowing for weekends & observed holidays and am unable to use the ATP due to org policy and prefer not to use VBA.
These are the formulas used in CF. I have deducted 1 day as the first date is not counted towards the KPI of 5 work days:
Formula:
=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A11&":"&$B$3)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A11&":"&$B$3)),Holidays,0))))-1)>5 (True = red)
=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A11&":"&$B$3)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A11&":"&$B$3)),Holidays,0))))-1)=5 (True = amber)
=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A11&":"&$B$3)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A11&":"&$B$3)),Holidays,0))))-1)<5 (True = green)
This has raised a new issue of summing cells in Col B where the number of work days <= 5. I will start a new thread for this.
Hope this helps someone.
Bookmarks