Skip to main content

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

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')

)

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!

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!


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

Reply


Terms & Conditions