Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 17, 2022 02:59 AM
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
Solved! Go to Solution.
Feb 17, 2022 02:21 PM
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!
Feb 17, 2022 05:22 AM
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')
)
Feb 17, 2022 02:21 PM
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!
Feb 17, 2022 10:33 PM
Oh that’s so smart! Thank you very much Ben! :raised_hands: