Format the outcome of IF()


#1

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?


#2

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.


#3

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


#4

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.


#5

Works both ways for me:

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


#6

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.