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

ionam

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

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:

1. If [Assessment Date Booked]@row is greater than [Deadline 2]@row, it returns "N/A".
2. If [Assessment Date Booked]@row is less than [Deadline 1]@row, it adds 730 days to [Assessment Date Booked]@row.
3. 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

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

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

ionam

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

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:

1. If [Assessment Date Booked]@row is greater than [Deadline 2]@row, it returns "N/A".
2. If [Assessment Date Booked]@row is less than [Deadline 1]@row, it adds 730 days to [Assessment Date Booked]@row.
3. 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

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

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

☺️