IF Statement
I'm struggling with an IF statement and I'm hoping I might get some help.
I'm trying to calculate the due date using the date the request comes in and the timeframe to respond (# days). However, there is the ability to extend the request up to two times, for various reasons. The first formula below worked great for one extension (which is triggered using a checkbox). However, I'm not sure how to add to this formula to essentially say, if the second extension box is extended then the timeframe should be multiplied by 3.
WORKING FORMULA WITH ONE EXTENSION: =IFERROR(IF([Request Extended]@row = 0, [Date of Request]@row + [Timeframe to Respond]@row, [Date of Request]@row + [Timeframe to Respond]@row * 2), "")
Anyone have any ideas?
I tried this: =IF([Second Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 3, IF([Request Extended]@row = 0, [Date of Request]@row + [Timeframe to Respond]@row * 3, [Date of Request]@row + [Timeframe to Respond]@row)) but it isn't calculating right when neither extension box is checked.
Best Answer
-
Hi both @SolutionSal and @.Chris - thank you for your help! Sal's formula worked with one change; instead of *3 in the second row, it needed to be *2. The below is the final formula and it is working as desired! Thanks so much!
=IF([Second Request Extended]@row = 1, [Date of Request]@row + ([Timeframe to Respond]@row * 3), IF([Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 2, [Date of Request]@row + [Timeframe to Respond]@row))
Answers
-
Hello, I think you mean to have the logical_expression in your second IF statement look like this
[Request Extended]@row = 1
instead of[Request Extended]@row = 0
Right now it's extending the due date if [Request Extended] is unchecked - a value of zero. If I'm right, your formula should instead be
=IF([Second Request Extended]@row = 1, [Date of Request]@row + ([Timeframe to Respond]@row * 3), IF([Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 3, [Date of Request]@row + [Timeframe to Respond]@row))
-
Hello @jtl
If I'm understanding your question correctly would something like this work?
=IF(CONTAINS("x", [Second Request Extended]@row), ([Timeframe to Respond]@row * 3 + [Date of Request]@row), IF(CONTAINS("x", [Request Extended]@row), ([Timeframe to Respond]@row * 2 + [Date of Request]@row), ([Timeframe to Respond]@row + [Date of Request]@row)))
I hope you find this helpful!
https://www.linkedin.com/in/zchrispalmer/
-
Hi both @SolutionSal and @.Chris - thank you for your help! Sal's formula worked with one change; instead of *3 in the second row, it needed to be *2. The below is the final formula and it is working as desired! Thanks so much!
=IF([Second Request Extended]@row = 1, [Date of Request]@row + ([Timeframe to Respond]@row * 3), IF([Request Extended]@row = 1, [Date of Request]@row + [Timeframe to Respond]@row * 2, [Date of Request]@row + [Timeframe to Respond]@row))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!