Add Jobs to rows on Calendar that are on Weekdays only OR Weekends only

Hi,

I need to add some extra functionality to a function that looks at a date range (K:L) from a list of work orders and adds Job_Codes (T) to a calendar.

The original formula I'm using was given to me on a previous thread:

https://www.excelforum.com/excel-for...-priority.html

The actual code I ended up using is below:
NB: This formula uses Names so will only make sense along with the attached example

Please Login or Register  to view this content.

(which I adapted very slightly from the second code given to me on the thread)



I've since added a new column (M) 'Work_Days' in the work order which has options of: "All Days", "Weekdays", "Weekends" & "".

See attached Example

I need to adapt the function somehow so that it assesses this new column and only adds Job_Codes (T) to the calendar that are on the selected type of days (M) between the date range (K:L).

i.e.
"Weekdays" only populates Mon, Tue, Wed, Thu, Fri
"Weekends" only populates Sat ,Sun
"All Days" populates every day (This is current behaviour)
"" populates no days

Thanks in advance for any help with this.

Try the following array entered formula*:

Formula:

Please Login or Register  to view this content.

*1) Select cell W11,
2) Paste the formula into the formula bar,
3) Activate by simultaneously pressing the Ctrl, Shift and Enter keys,
4) Copy down to W15 and (while the range W10:W15 is still selected) across to AC15.
Test by changing M3 to 'Weekends','All Days' etc.
Note: The formula already seems to address the concern raised in post #2 (this thread).
Let us know if you have any questions.

Try:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Hello.

I need help to develop this formula a bit further if possible

It's been serving me well up until now, but I now need to add functionality to include a list of defined Holiday days and treat these as weekend days.

My list of Holiday days is the range $B$52:$B$70

So the holiday dates should be used when Work_Days="Weekends" or "Every_Day" and not used when Work_Days="Weekdays"

My adapted version of the code I was given above now looks like this (in cell BP28):

Please Login or Register  to view this content.

I've attached my most recent version of the workbook which has developed quite significantly since the last post.

If you look at the booking for Company A (in the row of I23) between dates 20/12/2017 and 31/12/2017 and compare to the Calendar view on the right, you'll see that the dates 25/12/2017 & 26/12/2017 need to be displaying the booking (as M23 is set to weekends) and 25/12/2017 & 26/12/2017 are listed in my Holiday Range in $B$52:$B$70.

(Currently the booking only displays on the weekends and not on Christmas Day & Boxing Day as desired)


I've been adapting other bits of code I have using NETWORKDAYS.INTL to specify the holiday range, but I can't figure out how to do it for this code.


Massive thanks in advance for any assistance on this.