ExcelEndeavor
New Member
- Joined
- Oct 13, 2020
- Messages
- 11
- Office Version
- 365
- Platform
- 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.
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.
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?
Thank you in advance.