SOLVED

# How to calculate multiple due dates using Sumif and Workday functions

Copper Contributor

# How to calculate multiple due dates using Sumif and Workday functions

Hello,

I am trying to calculate the due date for multiple projects based on an indicator. The deadlines are tracked by weekdays only. I attempted to achieve this by using the function below. Excel keeps kicking the function back with the generic 'There is a problem with this formula' dialogue box'.

=IFS([@Indicator]=0,(=WORKDAY([@[Assigned Date]]),14),[@Indicator]=1,(=WORKDAY([@[Assigned Date]]),10))

My goal for the example above is to add '10' workdays to the assigned date if the indicator is '0' and if the indicator is '1' to add '14' workdays to the assigned date.

Any guidance on this would be greatly appreciated!

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: How to calculate multiple due dates using Sumif and Workday functions

=IFS([@Indicator]=0,WORKDAY([@[Assigned Date]],10),[@Indicator]=1,WORKDAY([@[Assigned Date]],14))

This formula works in my sheet.

# Re: How to calculate multiple due dates using Sumif and Workday functions

Thanks for this! the only difference between our formulas is I closed the WORKDAY function in parenthesis (which should not matter. When I removed the parenthesis to match your function it worked! might be a weird bug? Do you get the same result?

Either way, thank you for the reply! You just saved me a pretty major headache!

# Re: How to calculate multiple due dates using Sumif and Workday functions

=IFS([@Indicator]=0,  (WORKDAY([@[Assigned Date]],10))  ,[@Indicator]=1,

(WORKDAY([@[Assigned Date]],14)) )

You are welcome! If i put the WORKDAY function in parenthesis like above (highlighted in red) it works. I have to remove the equal sign before the WORKDAY function otherwise it doesn't work. I think in the other formula the closing parenthesis of the WORKDAY function are after [@[Assigned Date]] and should be after ,10) and after ,14).

# Re: How to calculate multiple due dates using Sumif and Workday functions

Yes! you are totally right! I hadn't noticed that I left the equal sign before the WORKDAY function, and forgot to close parenthesis after 10 and 14. I am still getting the hang of writing functions inside functions in Excel.

Thank you again for your support!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: How to calculate multiple due dates using Sumif and Workday functions

=IFS([@Indicator]=0,WORKDAY([@[Assigned Date]],10),[@Indicator]=1,WORKDAY([@[Assigned Date]],14))

This formula works in my sheet.