Combining IF statements

Angelique1

Hi Smartsheet community,

I am currently trying to create a formula, that states if Updated Completed Date is blank then use the date in Original Completion Date column but if not blank then use the date in the Updated Completed Date column to return quarter and year (Q4 2020). I am creating this formula in the Estimated Completion Quarter column. Any help will be greatly appreciated. Thanks!

hollyconradsmith

Hi @Angelique1,

I think you would need a helper column for that. The helper column would be something like this:

If(MONTH([updated completed date]@row)<4, "Q1 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>3<7, "Q2 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>6<10, "Q3 " + YEAR([updated completed date]@row), "Q4 " + YEAR([updated completed date]@row)

https://help.smartsheet.com/function/month

Mike Raposo

Hi,

A basic IF() formula should work for what you're looking for, something like this:

=IF([Updated Completed Date (FCI)]@row = "", [Original Completion Date (FCI)]@row, [Updated Completed Date (FCI)]@row)

Hope this helps! Let me know if you have any questions.

Best,

Mike

hollyconradsmith

Hi @Angelique1

Try something like this, using your real column names.

=If(not(isblank([updated completed date]@row)), [original completed date]@row, [updated completed date]@row)

Angelique1

Thank you both! Is there any way to have the output read as quarter and year for example: Q1 2021, Q2 2021, Q1 2022, etc.?

hollyconradsmith

Hi @Angelique1,

I think you would need a helper column for that. The helper column would be something like this:

If(MONTH([updated completed date]@row)<4, "Q1 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>3<7, "Q2 " + YEAR([updated completed date]@row), If(MONTH([updated completed date]@row)>6<10, "Q3 " + YEAR([updated completed date]@row), "Q4 " + YEAR([updated completed date]@row)

https://help.smartsheet.com/function/month