CourtneahThomas
4 years agoQrew Member
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
------------------------------
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
------------------------------