Formula for Counting Days based on 2 Scenarios

ExcelEndeavor

New Member
Joined
Oct 13, 2020
Messages
5
Office Version
  1. 365
Platform
  1. MacOS
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.


Tasks.jpg



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?

Thank you in advance.
 

Some videos you may like

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
Joined
Oct 14, 2016
Messages
3,616
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,600
Messages
5,573,147
Members
412,507
Latest member
Sarge24
Top