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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!