WORKDAY Function check specific location Holidays

Visitor

Hi Guys

 

I need to set the next good business day for a calculation based on a specific holiday calendar for London for example. I have a large data set for ALL holidays. I need to return the full list of holidays as an input to the Holidays argument where the location or currency matches my data set. An XLOOKUP will only return the first value so I was investigating using a combination of MATCH, ROW and INDEX). Perhaps a simple IF statement could work

 

Holidays -   Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

 

example below 

 

=WORKDAY(23/12/2022,1,IF(London = Location, DATES A1:A20,"")

 

=WORKDAY(G2,1,IF(E13=Table8[[All],[CCY]],Table8[Hols],"")) returns hash VALUE

 

Any help much appreciated

 

Thanks

 

Rory

1 Reply

@Rory22 

 

Something like this may work for you. The key is the FALSE return cannot be text or you get a #VALUE error if the location is not London.

 

=WORKDAY(D1,1,IF(C1="London",holidays,0))