I am trying to do multiple if statements in a formula but can't seem to get it right!
Here is my formula.
=IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, "[Assessment Date Booked]@row+730", IF([Assessment Date Booked]@row < [Deadline 2]@row, "[Assessment Date Booked]@row+365")))
What it is returning is [Assessment date booked]@row + 730 but I want it to add to years to the date in assessment date booked!
Can someone help?
Best Answers
-
Try this and be sure that your column types are Dates:
=IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, [Assessment Date Booked]@row + 730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row + 365, "")))
This revised formula checks:- If
[Assessment Date Booked]@row
is greater than[Deadline 2]@row
, it returns "N/A". - If
[Assessment Date Booked]@row
is less than[Deadline 1]@row
, it adds 730 days to[Assessment Date Booked]@row
. - If
[Assessment Date Booked]@row
is less than[Deadline 2]@row
but not less than[Deadline 1]@row
, it adds 365 days to[Assessment Date Booked]@row
.
If none of these conditions are met, it returns an empty string by default.
Shoutout to ChaptGPT for the assist~ - If
-
Hey @ionam,
Looks like you removed the wrong character here:
"Assessment Date Booked]@row+730
Remove the " at the beginning and put the [ in its place.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
Answers
-
The quotes around "[Assessment date booked]@row + 730" makes it a string of text. Simply remove the quotes.
-
That leaves me with
=IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, "Assessment Date Booked]@row+730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row+365)))
which results in #UNPARSEABLE
-
Try this and be sure that your column types are Dates:
=IF([Assessment Date Booked]@row > [Deadline 2]@row, "N/A", IF([Assessment Date Booked]@row < [Deadline 1]@row, [Assessment Date Booked]@row + 730, IF([Assessment Date Booked]@row < [Deadline 2]@row, [Assessment Date Booked]@row + 365, "")))
This revised formula checks:- If
[Assessment Date Booked]@row
is greater than[Deadline 2]@row
, it returns "N/A". - If
[Assessment Date Booked]@row
is less than[Deadline 1]@row
, it adds 730 days to[Assessment Date Booked]@row
. - If
[Assessment Date Booked]@row
is less than[Deadline 2]@row
but not less than[Deadline 1]@row
, it adds 365 days to[Assessment Date Booked]@row
.
If none of these conditions are met, it returns an empty string by default.
Shoutout to ChaptGPT for the assist~ - If
-
Hey @ionam,
Looks like you removed the wrong character here:
"Assessment Date Booked]@row+730
Remove the " at the beginning and put the [ in its place.
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
@AaronAtWork and @bisaacs
Thanks for this.
I have been at this tracker all day and you have really helped me out here. Time to hang it up for the weekend I think.
Have a good one and much appreciation for your help here.
☺️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!