Trying to use IF and OR in combination for given scenerio
Hi,
I have a requirement were I am checking values in 2 column using IF and OR and basis that the formula should return date after pre-defined days. I tried following statement but is it giving #INVALID ARGUMENT error.
=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), DATE([Start Date]@row + 17), IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), DATE([Start Date]@row + 30)))
Any help on this?
Best Answer
-
The date function is being used incorrectly and is not needed for this formula.
Try the below function where i removed the DATE() function.
=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + 17, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), [Start Date]@row + 30))
Answers
-
The date function is being used incorrectly and is not needed for this formula.
Try the below function where i removed the DATE() function.
=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + 17, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), [Start Date]@row + 30))
-
@Leibel S Thanks a lot it worked
-
I am tried to make the above argument more generic and added column reference instead of manually adding number of days.
=IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), DATE([Start Date]@row + Duration@row, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), DATE([Start Date]@row + Duration@row))))
But it is giving #INVALID DATA TYPE error.
Would appreciate quick help on this.
Thanks in anticipation.
-
you added the date function in again... Remove that and it should work.
-
@Leibel S wow its is working...thanks.
one more thing that I found problematic is that if the one of the reference columns is blank it is throwing #INVALID OPERATION error.
Thanks again for helping!
-
Wrap the entire function in an IFERROR()
=IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), [Start Date]@row + Duration@row, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), [Start Date]@row + Duration@row)),"")
-
@Leibel S that's great,
I have one more query to bother may be how I can modify/frame this formula so that it does calculate ONLY the working days?
-
I tried adding WORKDAY function, but giving #INVALID ARGUMENT error:
=IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + (WORKDAY([Start Date]@row, Duration@row)), IF(OR(Level@row = 1, [New/Existing]@row = "Existing Request"), [Start Date]@row + (WORKDAY([Start Date]@row, Duration@row)))))
Any immediate help on this?
-
See below corrected syntax
=IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), WORKDAY([Start Date]@row,Duration@row), IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), WORKDAY([Start Date]@row,Duration@row))),"")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!