Exclude Weekends in Dynamic Range Controlled Schedule Template

I've created the attached scheduling template from a document that someone had given me. It's a very elegant scheduling tool, creating from dynamic ranges.

I need to have the weekends excluded from the planned duration. For example, a task starting on monday and lsting six days should conclude on the following Monday (Typically). I have been unable to figure this in to the duration.

I was able to correct the weekends in the conditional formatting that completes the bar chart by using =AND(Plan,NOT(WEEKDAY(I$10)=1),NOT(WEEKDAY(I$10)=7)) .

I'm definitely not an expert using named ranges, but I'm hoping this can be resolved simply. This scheduling template would be used by end-users that are not savvy on start dates/finish dates/etc., that is why I am using time periods for each day.

I've created a named range titled WorkdayCount that somewhat accomplishes what I am trying to do; however it is glitchy. The formula for that range is =EVEN(DAYS(PDS!M$10+PDS!$D14,PDS!M$10)-NETWORKDAYS(PDS!M$10,PDS!M$10+PDS!$D14)). In theory, it should take the total days, and subtract the working days, and then spit the result (rounded up to the next even number) to the duration. However, as I said, it doesn't always work as desired, especially if the result of that adjustment puts the end of duration into another weekend.

I'm hoping someone who is a Range Master (LOL) can take a look at this and get me on the right path.

EDIT:

Hi jltarley. Welcome to the forum.

EDIT: Can I not attach a file to this?

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.