Nested IF with “!=blank()”

[Vic]

Hey guys,

I’m having some troubles with the blank formula. I build this simple task-manager. The idea is that i can choose a task-startdate and choose with the single select the duration. Then I have a formula which counts based on the single select the right end-date.

When there is no date, I get an error, which I would like to remove.

This is the original formula for the end-date:

IF(Block = "2 Std", DATEADD(Datum, 2, 'hour'), IF(Block = "4 Std", DATEADD(Datum, 4, 'hour'),IF(Block = "1 Std", DATEADD(Datum, 1, 'hour'),IF(Block = "8 Std", DATEADD(Datum, 8, 'hour'),IF(Block = "0,5 Std", DATEADD(Datum, 0.5, 'hour'))))))

This is the one I tried with blank, but I’m doing something wrong here…

IF(Block = "2 Std", DATEADD(Datum, 2, 'hour'), IF(Block = "4 Std", DATEADD(Datum, 4, 'hour'),IF(Block = "1 Std", DATEADD(Datum, 1, 'hour'),IF(Block = "8 Std", DATEADD(Datum, 8, 'hour'),IF(Block = "0,5 Std", DATEADD(Datum, 0.5, 'hour', IF(Datum = BLANK())))))))

Thank you very much!

Best,
Viktoriya

[Alexey_Gusev]

Hi, Viktoriya

When you have several options, I would recommend to use SWITCH.
In you case, you can convert option to numeric value and use.

IF(Datum,
DATEADD(Datum, 
VALUE(SUBSTITUTE(Block,' Std',''))
, 'hour')
)

[Ben_Young1 · Accepted solution]

Hello @Vic! Welcome in!

Here’s my version of your formula.

IF(
    AND(
        {Block},
        {Datum}
    ),
    IF(
        {Block} = "2 Std",
        DATEADD(
            {Datum}, 2, 'hour'
        ),
        IF(
            {Block} = "4 Std",
            DATEADD(
                {Datum}, 4, 'hour'
            ),
            IF(
                {Block} = "1 Std",
                DATEADD(
                    {Datum}, 1, 'hour'
                ),
                IF(
                    {Block} = "8 Std",
                    DATEADD(
                        {Datum}, 8, 'hour'
                    ),
                    IF(
                        {Block} = "0,5 Std",
                        DATEADD(
                            {Datum}, 0.5, 'hour'
                        )
                    )
                )
            )
        )
    )
)

In your original formula, you were not specifying what conditions had to be met in order for the DATEADD() function to be evaluated.

With this version, the formula will only return a value once both the Datum field and the Block fields have values in them.

Unless those two conditions are met, there will always be a blank value.

Let me know if you have any questions or want to tweak it a bit!

[Vic]

Oh that’s so smart! Thank you very much Ben! :raised_hands: