Find First Workday of Month Value Excl Holiday

Ok, so I’m trying to come up with a single cell formula that will find for me the value corresponding to the first weekday of a daily time series – ignoring specified holidays.

In the attached worksheet under “Current State,” you can see the formula I’m using to determine the first weekday (cell K5) and retrieve the inventory level corresponding to that day (cell K6).

The section underneath, “Ideal State,” shows what I want to accomplish (all hardwired of course for now). Note the specified holidays (name: arrayHolidays). Cell K17 should refer to this array and see that 4/1 is a designated holiday and move on. 4/2 and 4/3 is the weekend so it lands on 4/4 as the first applicable weekday.

Any advice on how to formulate cell K17 would be greatly appreciated – and please don’t feel obligated to follow the same logic as K5 (which I suspect could be more elegant in itself).

Use the WORKDAY function.

=workday(B5,0,G20:G24)

Does that work?

Originally Posted by brokenbiscuits

Use the WORKDAY function.

=workday(B5,0,G20:G24)

Does that work?

Thanks for the response, broken.

Unfortunately, workday in itself won’t work. It’s still returning 4/1 as the first applicable workday despite reference to the 4/1 holiday.

I am using “NETWORKDAYS(B5,TODAY(),Holidays)” to count the number of applicable days but I am having trouble applying this formula to pick out the date itself. I’m wondering if I can use networkdays in conjunction with offset to get me there?

Originally Posted by martindwilson

check your dates one says 04/01 the other 04/04

what my formula gives me now vs what i want it to give me.

Originally Posted by the.ronin

In the attached worksheet under “Current State,” you can see the formula I’m using to determine the first weekday (cell K5) and retrieve the inventory level corresponding to that day (cell K6).

The section underneath, “Ideal State,” shows what I want to accomplish (all hardwired of course for now).

=WORKDAY(EOMONTH(B5, -1), 1, arrayHolidays)