How to write multiple IF conditions for different number of working days to be counted in Excel

Question

How to write multiple IF conditions for different number of working days to be counted in Excel

Clemetson, Travis B 0

I had a formula that calculate the number of the working days (excluding the weekends, holidays). Currently the formula (within Column T) is set to calculate 5 working days out from a manually inputted date (Column H). However, I am now needing to add additional factors. within Column M are 3 different categories were within category 1 or 2 are chosen, the formula calculates 5 working days out from the date in Column H. However if Category 3 is selected, I need the formula to calculate 7 working days from the date entered within Column H. Below is the formula I am currently utilizing with AK$2:AK$71 being my list of holidays.

=IF((H539=""),"_",IF(WEEKDAY(H539)=1,WORKDAY(H539,6,AK$2:AK$71),IF(WEEKDAY(H539)=7,WORKDAY(H539,6,AK$2:AK$71),WORKDAY(H539,5,AK$2:AK$71))))

Barry Schwarz 3,746 Reputation points

2023-11-21T00:40:11.2666667+00:00
I don't understand why you treat Saturday and Sunday special. The WORKDAY function automatically excludes weekends from its computations. In the absence of a holiday, five workdays after Saturday is the next Friday. The same for Sunday. Five days after a weekday is the same day next week. Unless you want to treat anything starting on a weekend as actually starting on the following Monday, you don't need to care about which day of the week is the starting day.

So it looks like the generic expression

WORKDAY(H539,5,AK$2:AK$71)

will do you what you want. However, if you really need to add an extra day for a weekend start, then something like

WORKDAY(H539,IF(WEEKDAY(H539,3)<5,5,6),AK$2:AK$71)

will do it without nested IF functions and redundant sub-expressions.

Finally, if you explain what you mean by categories, we probably can figure out a way to make that fit in also.
Jiajing Hua 18,060 Reputation points Moderator

2023-11-21T07:20:02.1133333+00:00

Hi @Clemetson, Travis B

For the Workday formula, I agree with Barry Schwarz.

But if you need to add IF conditions in Column M, could you please share us a simple sample that with the results that you need?

Such as if the day is Saturday (WEEKDAY(H539)=7), but the M539 is Category 3, will you calculate 7 working days or others?

Any updates, please let us know.
Clemetson, Travis B 0 Reputation points

2023-11-21T16:14:04.6433333+00:00

Good Morning,

@Jiajing Hua So the example is that Column H is the starting date of my action can use today (11/21/23) as an example with a Category 3 Within Column M. What I currently have is the equation that will calculate 7 working days excluding holidays and weekends within Column T to give me a due by Date, in this case being 12/4/23. What I am needing is a equation that will change the working day formula days formula in Column T based on Column M. Using this same example if I changed the category to 1 or 2, for the formula to automatically change from calculating 7 working days to calculating 5 workings days, giving my the updated due date of 11/30/23.

@Barry Schwarz What I mean by "categories" is simply what I called the 3 different values within Column M that would influence what the formula is. Originally I was thinking I would need a nested formula that would need each of those different formulas for each categorically but wasn't sure if it was necessary needed.

I hope that clarification helps
Clemetson, Travis B 0 Reputation points

2023-11-21T16:45:16.8333333+00:00

If you would like additional information such as a possible screenshot of the end result, I can draft the "ideal" end result.

1 answer

Your answer

Barry Schwarz 3,746 Reputation points

2023-11-21T00:40:11.2666667+00:00

I don't understand why you treat Saturday and Sunday special. The WORKDAY function automatically excludes weekends from its computations. In the absence of a holiday, five workdays after Saturday is the next Friday. The same for Sunday. Five days after a weekday is the same day next week. Unless you want to treat anything starting on a weekend as actually starting on the following Monday, you don't need to care about which day of the week is the starting day.

So it looks like the generic expression

WORKDAY(H539,5,AK$2:AK$71)

will do you what you want. However, if you really need to add an extra day for a weekend start, then something like

WORKDAY(H539,IF(WEEKDAY(H539,3)<5,5,6),AK$2:AK$71)

will do it without nested IF functions and redundant sub-expressions.

Finally, if you explain what you mean by categories, we probably can figure out a way to make that fit in also.
Jiajing Hua 18,060 Reputation points Moderator

2023-11-21T07:20:02.1133333+00:00

Hi @Clemetson, Travis B

For the Workday formula, I agree with Barry Schwarz.

But if you need to add IF conditions in Column M, could you please share us a simple sample that with the results that you need?

Such as if the day is Saturday (WEEKDAY(H539)=7), but the M539 is Category 3, will you calculate 7 working days or others?

Any updates, please let us know.
Clemetson, Travis B 0 Reputation points

2023-11-21T16:14:04.6433333+00:00

Good Morning,

@Jiajing Hua So the example is that Column H is the starting date of my action can use today (11/21/23) as an example with a Category 3 Within Column M. What I currently have is the equation that will calculate 7 working days excluding holidays and weekends within Column T to give me a due by Date, in this case being 12/4/23. What I am needing is a equation that will change the working day formula days formula in Column T based on Column M. Using this same example if I changed the category to 1 or 2, for the formula to automatically change from calculating 7 working days to calculating 5 workings days, giving my the updated due date of 11/30/23.

@Barry Schwarz What I mean by "categories" is simply what I called the 3 different values within Column M that would influence what the formula is. Originally I was thinking I would need a nested formula that would need each of those different formulas for each categorically but wasn't sure if it was necessary needed.

I hope that clarification helps
Clemetson, Travis B 0 Reputation points

2023-11-21T16:45:16.8333333+00:00

If you would like additional information such as a possible screenshot of the end result, I can draft the "ideal" end result.

Answer 1

Jiajing Hua 18,060 Moderator

Hi @Clemetson, Travis B

Thanks for your response.

Take '11/21/23' as an example, the result of WEEKDAY(H) is 3, under the case of your previous formula, it calculates 5 working days. But now, there is a Category 3 within Column M, you need to change the formula to calculate 7 working days. Am I right?

If so, you may try formula WORKDAY(H539,IF(M539=3,7,5),AK$2:AK$71).

Any misunderstandings, please feel free to let us know.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jiajing Hua 18,060 Reputation points Moderator

2023-11-27T05:59:02.08+00:00

Hi @Clemetson, Travis B

I am checking this thread, if you have a chance to be back, please check my updates.

Any further issues, welcome to discus with us.

Share via

How to write multiple IF conditions for different number of working days to be counted in Excel

1 answer

Your answer