Excel workday calculation

2
votes

I am using the following formula for a workday calculation where GB bank holidays are listed in sheet 'Bank Holidays' cell range B13:Z13.

=IF(F2<E2,IF(L2="GB",WORKDAY(E2,2,'Bank Holidays'!B13:Z13)),IF(L2="GB",WORKDAY(F2,2,'Bank Holidays'!B13:Z13)))

In the 'Bank Holidays' sheet I have 31 countries bank holidays listed across rows, column A has the list Country codes, columns B to Z have the holiday dates.

In my active worksheet ('ALL Data') I have rows of orders shipments including the country code (column L).

What I would like to do is when calculating the workday I want to exclude the holiday relevant to the country code.

In effect I need to do a vlookup using the country code on sheet 'ALL Data' Column L to the holiday days listed against the same country code on sheet 'Bank Holidays' column A.

But vlookup only returns the first value found. Is there a type of vlookup that will return all the dates against the relevant country code (range: column B:Z).

I believe an index match would work but, I'm not familiar with this and I cannot get this to work.

1
excelvlookup
INDEX and MATCH will be what you are looking for – Dirk Reichel
@DirkReichel don't think INDEX/MATCH is all that easy/clean of a solution in this case... – Scott Holtzman
Have you tried to google it? There are billions of examples showing how to use it (and also to avoid Lookup)... Even at this site are so many of them, you will have a hard time to ask a question wich is not a duplicate... but to simply throw the solution at him: =WORKDAY(MAX(E2,F2),2,INDEX('Bank Holidays'!B:Z,MATCH(L2,'Bank Holidays'!A:A,0))) – Dirk Reichel

1 Answers

2
votes

I actually OFFSET and MATCH would work easier for this particular case (but maybe it's just how I think about things in Excel):

=OFFSET('Bank Holidays'!A1,MATCH(L2,'Bank Holidays'!A:A,0)-1,1,1,24)

will get you the specific holidays for the country code in L2 in the Bank Holidays sheet.

So the entire formula becomes:

=IF(F2<E2,WORKDAY(E2,2,OFFSET('Bank Holidays'!A1,MATCH(L2,'Bank Holidays'!A:A,0)-1,1,1,24)),WORKDAY(F2,2,OFFSET('Bank Holidays'!A1,MATCH(L2,'Bank Holidays'!A:A,0)-1,1,1,24)))

The 24 column width argument assumes no holiday dates go beyond column Z for any given country.