# If or Case formula not working when I include a duration field

• 0
• Question
• Updated 4 years ago

Hello,

I'm pretty new (3 weeks in) but I seem to be picking most things up quickly. However, there is one formula which isn't working for me.

If([Capacity Charge Terms]="Pence per day", [Capacity for this site (kVa)] * [Capacity Charge (£ per kVa)] * [No. of Days in this Bill] * 100, [Capacity for this site (kVa)] * [Capacity Charge (£ per kVa)])

I've tried this too:

Case([Capacity Charge Terms],
"£ per month", [Capacity for this site (kVa)] * [Capacity Charge (£ per kVa)],
"Pence per day", [Capacity for this site (kVa)] * [Capacity Charge (£ per kVa)] * 100 * [No. of Days in this Bill], null)

I've established that when I take [No. of Days in this Bill] out of both the formulae, they both work fine, but I need that field in the calculation!  That field is a formula too:
[Bill End Date] - [Bill Start Date] + Days(1)

Any ideas why this duration formula field might be throwing it off, and how I can get around it, please?

• 0 Points

Posted 4 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 64,292 Points
It sounds like the field [No. of Days in this Bill] is of type duration.

I suggest that you consider changing its formula to be a  formula Numeric as opposed to a formula duration, given that it represents the # of days ... ie a number.

You could fix this formula like this

If([Capacity Charge Terms]="Pence per day", [Capacity for this site (kVa)] * [Capacity Charge (£ per kVa)]

* ToDays([No. of Days in this Bill])

* 100, [Capacity for this site (kVa)] * [Capacity Charge (£ per kVa)])

.. but like i said, I suggest that you get away from using duration fields in calculation fields involving numbers and amounts.

I'm guessing that your formula for [No. of Days in this Bill] is like

[Billing end date] - [Billing start date]

so I would suggest making the change to field type to be numeric and changing the formula to

ToDays([Billing end date] - [Billing start date])
• 0 Points
Perfect, Mark.  Thanks very much for your very prompt and correct answer!