Skip to Content

Create Estimated Formula for Future Time if Null

Aug 17, 2017 at 06:45 PM


avatar image
Former Member

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Abhilash Kumar
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}
    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.


Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Abhilash:

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