Hi I'm trying to use a formula that works in excel to work in smartsheet

TonyW

This is the formula

=IF([Actual Test Date]1 >= "", "MSA Required", IF(AND([Next MSA ]1 = "x"), "MAS Required", IF(AND([Actual Test Date]1 < [MSA Due Date]1), "MSA Completed", IF([Actual Test Date]1 > [MSA Due Date]1, "MSA Late", "MSA Completed"))))

but it gives #invalid operation... It look likes it is using the first statement then ignoring the following ifs.

Any help will be greatly appetited.

MichaelTCA

Hello @TonyW

Are you comparing actual dates or calculated values based on dates?

I would try changing the syntax.

Use ISBLANK() instead of >= blank. It's hard to get greater than blank. 😋

How come you are using the AND function with only 1 argument?

TonyW

Hi MichaelTCA, thanks for your answer.

I have the same formula in excel that works perfectly fine. My predecessor had created this formula.

So as I'm trying to "port" this over to smart sheet that is when I have run into problems.

But yes I'm using dates, I'm trying to say that is, if no date "Required", if actual date is less than due date "Completed"

if actual date greater than due date "late" if next msa date = "X" "Required".

Thanks again for your help so far.

MichaelTCA

Hello,

Ya, I use both excel and Smartsheet and some functions relate, but Smartsheet doesn't have all the same functions or use the same syntax with every function between the two software. Only a few.

[Next MSA ]1 - Is there supposed to be a space after MSA? This is within the first false statement.

I'm not too sure what you're doing, but based on the rest of the function, I assume this is supposed to read MSA?

IF(AND([Next MSA ]1 = "x"), "MAS Required"

TonyW

HI Michael.

Yes its supposed to say "MSA Required".

I have been able to make the following all on different columns that work individually. But I'm unsure how to connect them all as 1?

=IF(ISBLANK([Actual Test Date]1), "Required", "Completed"

=IF([Actual Test Date]1 < [MSA Due Date]1, "Completed", "Late")

=IF([Actual Test Date]1 > [Next MSA Study]1, "Required", "Completed") not sure I need the completed in this line?

Thanks in advance.

Paul Newcome

Give this a try:

=IF(OR([Actual Test Date]@row = "", [Actual Test Date]@row> [Next MSA Study]@row), "Required", IF([Actual Test Date]@row> [MSA Due Date]@row, "Late", "Completed"))

TonyW

Hi Paul.

Thanks for the help that works great.

Kind Regards,

Tony.

Paul Newcome

Happy to help. 👍️

TonyW

I have a 1 more question.

If I want to add in =IF([Next MSA ]1 = "x", "Required") how can I do this?

Paul Newcome

You would add it inside of the OR statement that outputs "Required".

=IF(OR([Actual Test Date]@row = "", [Actual Test Date]@row> [Next MSA Study]@row, [Next MSA Study]@row = "x"), "Required", IF([Actual Test Date]@row> [MSA Due Date]@row, "Late", "Completed"))

TonyW

Hi Paul

Thank you so much.

All works great.

Kind regards

Tony.

Paul Newcome

Happy to help. 👍️