0

I am trying to use the Excel "workday" function to assist with handling a large list of public and organisational holidays when scheduling education courses in a multi-state country (Australia). However, the large list of holidays contains locale-specific holidays! So, some holidays apply only to particular States, while others to all States (these are listed out for each state when applicable).

I'm looking for assistance to formulate a way to select/filter the array of holidays to the specific state I'm looking at while maintaining the list in a single table. If using an INDEX-MATCH type input, then I only return a single value. And this doesn't work if there are multiple sequential holidays.

For example, hypothetically, if I try to schedule an Tasmanian event on Easter Monday, the next working day will be the Wed (Tas have Easter Tue as a holiday), while other states could have an event on Tue (as no public holiday).

Is it possible to create a dynamic array in Excel to input to the Workday function?

Example data

12
  • Thanks @fixer1234 – Daniel K Jun 1 '19 at 20:35
  • Is the input going to be a start date, number of days and a State? e.g. what will be the workday 6 days after 18-Apr-19 for say state TAS? – patkim Jun 1 '19 at 21:04
  • If a holiday is nationwide, will that be listed any differently? Or will there be a separate line item for each state/holiday combination. – Ron Rosenfeld Jun 2 '19 at 1:18
  • perform your work day count without the special holidays in it, then determine the number of days between your start and end date and subtract that count from your work day count? – Forward Ed Jun 2 '19 at 4:12
  • @RonRosenfeld There's a separate line for each state and duplicated per state for national holidays – Gondy Jun 2 '19 at 6:10
0

ASSUMPTIONS:

  • Holiday list is sorted by state
  • Holidays that affect all states are entered for each state
  • Holidays that are a range (ie more than 1 day) have each day entered in the list

Determine the start row for your state holiday list:

AGGREGATE(15,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1)

Determine the end row for you state holiday list:

AGGREGATE(14,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1)

Use INDEX to get the cell address of the appropriate row

INDEX(holsheet!D:D,AGGREGATE(15,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1)):INDEX(holsheet!D:D,AGGREGATE(14,6,ROW(holsheet!$A$1:$A$20)/(somesheet!$B$3=holsheet!$A$1:$A$20),1))

you could set that as a named formula such a STATE_HOLIDAYS and then in your workday function just reference STATE_HOLIDAY instead of the big ugly formula.

=WORKDAY(C3,-3,STATE_HOLIDAYS) 
1
  • Thanks for this. I've never used the aggregate function before - so very interesting usage! – Gondy Jun 3 '19 at 7:05
1

If you have a Holiday Table with a column for the Holidays, and another for the State, you can use this to create an array of the relevant holidays (actually, an array of the relevant holidays and 0's; the 0's will be ignored by the Holidays argument in the function)

(HolidayTbl[State]=myState)*HolidayTbl[Holidays]

(I used structured references with the Table, but you can convert them to normal references if you don't want to use a Table)

The formula would then be:

=WORKDAY(StartDate,NumDays,(HolidayTbl[State]=myState)*HolidayTbl[Holidays])

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

enter image description here

3
  • Very simple solution; and I learned something about named tables, too! The sumproduct command seems to be a goto for array stuff! Very elegant! – Gondy Jun 3 '19 at 7:06
  • @Gondy And sorting is not required for this solution. – Ron Rosenfeld Jun 3 '19 at 9:47
  • +1 for no sorting required! – Forward Ed Jun 3 '19 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.