Skip to main content
Question

Formula help

  • October 19, 2021
  • 0 replies
  • 14 views

Forum|alt.badge.img+3
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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+22
  • Quickbase Alumni
  • October 19, 2021
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
------------------------------