I am trying to do multiple if statements in a formula but can't seem to get it right!

ionam · 05/17/24

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?

AaronAtWork · 05/17/24

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~

bisaacs · 05/17/24

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.

AaronAtWork · 05/17/24

The quotes around "[Assessment date booked]@row + 730" makes it a string of text. Simply remove the quotes.

ionam · 05/17/24

https://community.smartsheet.com/discussion/comment/436635#Comment_436635

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

AaronAtWork · 05/17/24

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~

bisaacs · 05/17/24

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.

ionam · 05/17/24

@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.

☺️

I am trying to do multiple if statements in a formula but can't seem to get it right!

Best Answers

Answers

Help Article Resources

Categories