Format the outcome of IF()

[Valerie_Vanderh]

Hi everyone,

So I’m encountering a problem: Below you can see the output of my IF() formula, the output is a duration, but I can’t seem to format it this way. I would like to format it in HH:MM, but I tried everything i could think of, but It won’t come out right. It stays, what i think is an integer number.

Any thoughts?

[Kamille_Parks]

Your formula needs to be IF({Date to Declined}!="",DATETIME_DIFF({DATE To DECLINED},Date,'seconds'),""). Then you need to click the “Formatting” in the Customize Field Options panel. Select “duration” from the dropdown.

[Elias_Gomez_Sai]

I clarify that the result must be seconds because that is the unit used by the Duration field.

[Noamsay]

It’s a bit tricky, but actually in order to format the result as a duration, you should not specify a unit as a third parameter
Also you need to use BLANK() instead of "" in the value returned by the IF() otherwise it will be formated as a string.
Your formula should be:

IF({Date to Declined}!="",DATETIME_DIFF({Date to Declined},Date),BLANK())

Then you should have the ability to format the result as a duration.

[Elias_Gomez_Sai]

Noamsay:

It’s a bit tricky, but actually in order to format the result as a duration, you should not specify a unit as a third parameter

Works both ways for me:

I think they mean you don't need the unit parameter :man_shrugging:

[W_Vann_Hall]

Noamsay:

Also you need to use BLANK() instead of "" in the value returned by the IF()

Actually, that closing BLANK() isn’t necessary (and leaving off the null response avoids any confusion over BLANK() vs '').

IF(
    {Date to Declined}!="",
    DATETIME_DIFF(
        {Date to Declined},
        Date
        )
    )

works fine.