Forum Discussion

CourtneahThomas's avatar
CourtneahThomas
Qrew Member
3 years ago

Formula help

Hello,

I have a formula - numeric field I am using to convert a duration field into a numeric field so I can create a total field.  How would I change my formula to treat blank as 0?

The original duration field formula, [Total Hours1]:

If(
ToDate([End Date/Time1]) > ToDate([Start Date/Time1]),
// This part calculates anything pass midnight
(ToTimestamp(ToDate([End Date/Time1]),[End Time1]) - ToTimestamp(ToDate([End Date/Time1]),ToTimeOfDay("12:00 am")))
// This part calculattes everything before midnight
+ (ToTimestamp(ToDate([End Date/Time1]),ToTimeOfDay("11:59 pm")) - ToTimestamp(ToDate([End Date/Time1]),[Start Time1])) + Minutes(1),
[End Time1]-[Start Time1]
)

The field I'm using to convert from duration to numeric, [ToHours-Total Hours1]:

ToHours([Total Hours1])

On numeric fields, Quickbase has a checkbox Treat blank values as "0" in calculations" but that isn't an option on the formula-numeric fields.  

Thank you!


------------------------------
Courtneah Thomas
------------------------------

1 Reply

  • try this.  I'm using the Nz function to convert any null to a zero
    https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=112&rl=dv

    If(
    ToDate([End Date/Time1]) > ToDate([Start Date/Time1]),
    // This part calculates anything pass midnight
    (ToTimestamp(ToDate([End Date/Time1]),[End Time1]) - ToTimestamp(ToDate([End Date/Time1]),ToTimeOfDay("12:00 am")))
    // This part calculates everything before midnight
    + (ToTimestamp(ToDate([End Date/Time1]),ToTimeOfDay("11:59 pm")) - ToTimestamp(ToDate([End Date/Time1]),[Start Time1])) + Minutes(1),
    Nz([End Time1]-[Start Time1])
    )

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------