Skip to Content
avatar image
Former Member

Create Estimated Formula for Future Time if Null

Here is my situation: I have a list of work to be done for the day and for each of them there is a time associated if it's done. In the case that the work hasn't been done, I need to "fake" the future value by estimating what they are likely to be done by (by default, 15 minutes)

Here is what it looks like:

Work1 6:28.26

Work2 7:11.36

Work3 7:14.18

Work4 N/A

Work5 N/A

The estimated result for Work4 and Work5 should be respectively 7:29.18 and 7:44.18 and lighlighted in red to show that they are estimated and not actual value. I tried using

if isnull({Command.PackedTime}) then dateadd('n', +15, previous ({Command.PackedTime})) else {Command.PackedTime}

but it doesn't seem to work. Is there a way a can achieve this goal?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 18, 2017 at 07:46 AM

    Hi Cyril,

    Create a formula with this code:

    shared datetimevar t;
    if NOT isnull({Command.PackedTime}) OR {Command.PackedTime} <> datetime(0,0,0,0,0,0) then
    t := {Command.PackedTime}
    Else
    (
        t := dateadd('n', +15, t)
    );

    In the formula editor, look for a dropdown on the top that says 'Exceptions for Nulls', change that to 'Default Values for Nulls'.

    Place the formula on the Details Section.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Abhilash:

      Thanks so much for your solution, it does exactly what I was looking for.

      Cheers,

      Cyril