Formula for Counting Days based on 2 Scenarios

ExcelEndeavor

New Member
I have a timeline that has a user input the start date and the number of days for each task to be completed, which will generate a completion date. Here's where it gets confusing -

For tasks that take 5 days or less, I CAN'T count weekend days or holidays.
For tasks that take 6 days or more, I CAN count weekend days and holidays, but the completion date CAN'T land on a weekend day or holiday.

The screenshot below shows the start date in blue (G7) and the number of days in blue (C8,C9,C10), the result will populate the red dates. The blue cells are user inputs, so they will be varied. The list of holidays is listed at E29:E38.

I have two formulas that are working great so far:

For tasks that take 5 days or less, I am using:

=IF(G7="","",WORKDAY(G7,C9,E29:E38))

For tasks that take 6 days or more, I am using:

=IF(G7="","",IF(WEEKDAY(G10)=7,WORKDAY(G10,1,E29:E38),IF(WEEKDAY(G10)=1,WORKDAY(G10,1,E29:E38),WORKDAY(G10-1,1,E29:E38))))

Sometimes tasks might take longer than the 2 day or 5 day examples that I have in the screenshot. Is there a way to combine these formulas into one, so when the user inputs the number of days less than 5 or more than 6, it will automatically count the correct days and holidays?

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Tetra201

MrExcel MVP
Maybe

=IF(\$G\$7="","",IF(C9<6,WORKDAY(\$G\$7,C9,\$E\$29:\$E\$38),WORKDAY(\$G\$7+C9-1,1,\$E\$29:\$E\$38)))

ExcelEndeavor

New Member
Maybe

=IF(\$G\$7="","",IF(C9<6,WORKDAY(\$G\$7,C9,\$E\$29:\$E\$38),WORKDAY(\$G\$7+C9-1,1,\$E\$29:\$E\$38)))

That didn't work.

Replies
3
Views
84
Replies
1
Views
190
Replies
3
Views
180
Replies
1
Views
151
Replies
2
Views
259