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

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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?

Photo of Donnie

Donnie

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,944 Points 50k badge 2x thumb
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])
Photo of Donnie

Donnie

  • 0 Points
Perfect, Mark.  Thanks very much for your very prompt and correct answer!