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
------------------------------
• 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
------------------------------