Missing formula function. Please help determine!

Mr. Z

Good morning Smartsheet Community. Hope you all are well. I'm looking for help to find the proper function to help me achieve what I'm trying to do.

I have three columns seen in the screen shot below, Rough In Date, Install Date and Tech Date.

I currently have the following formula in the Tech Date Column.

=IFERROR(IF(WEEKNUMBER([Rough In Date]@row) = WEEKNUMBER(TODAY()), [Rough In Date]@row, IF(WEEKNUMBER([Install Date]@row) = WEEKNUMBER(TODAY()), [Install Date]@row)), " ")

The above formula is working perfect but only when there is both a date entered into the Rough In Date column and the Install Date column. Problem is that not all projects we do have a Rough In Date. There for the formula doesn't run.

I'm looking for help determining the proper function to use with this to help make it run even when Rough In Date is blank.

Any help you can provide would be greatly appreciated.

SSFeatures

Hi @Mr. Z,

Try this formula:

=IF(
    IFERROR(WEEKNUMBER([Rough In Date]@row), -1) = WEEKNUMBER(TODAY()),
    [Rough In Date]@row,
    IF(
        IFERROR(WEEKNUMBER([Install Date]@row), -1) = WEEKNUMBER(TODAY()),
        [Install Date]@row,
        ""
    )
)

Which worked in this screenshot:

It turns out that if the cells is empty, then doing WEEKNUMBER(…) causes an "#Invalid Data Type" error. So I just wrapped those comparisons in an IFERROR.

Hope this helps!

Mr. Z

@SSFeatures Thanks for the help. Greatly appreciated. I tried a lot of things but never thought to move the IFERROR to the inside and flip it with the IF.

SSFeatures

@Mr. Z You're welcome! Haha yea it took me a bit to think of doing it that way too.

Glad it's working now!