# Formula to Calculate Amount Due

• 38
• Question
• Updated 6 months ago
I have a table called Task with a one to many relationship to another table called Criteria.

On the Criteria table I have the following fields:

• [Task - Date Identified] (lookup Date/Time) - looks up the date in which the Task was identified.

• [Date Complete]- Date/Time - tells at what date & time criteria was complete.

• [Deduction Period] - Duration (days/hours) - Tells us how long we have to complete the task criteria before we begin to be charged

• [Deduction]- Currency - tell us how much we will be charged for each deduction period after the allowed time

• Deficiency Deductions Start] - Formula Date/Time - tells us what time or date the deductions are going to start -
• ([Task - Date Identified]+[Deduction Period])

• [Time Used] - Formula Numeric - Tell us how much time we have used to complete the task criteria

• [% of Days Used] - Formula % - Used for a Performance Bar formula
• Days([Time Used])/[Deduction Period]

• [Time Overdue] - Formula Duration - tell us the amount of time we went past the Deduction Period
• If(IsNull([Date Complete]) and Days([Time Used])>[Deduction Period],(Now()-[Deficiency Deductions Start]),
(not IsNull([Date Complete]) and Days([Time Used])>[Deduction Period]), ([Date Complete]-[Deficiency Deductions Start]))

• [Total Deduction] - Formula Currency - calculates how much we should deduct from our monthly invoice.
• ([Time Overdue]*[Deduction])

Now, here is the problem.  My Total Deduction formula isn't working.

The formula I am using for the Deduction Starts field is:

The formula for Time Overdue is:
If(IsNull([Date Complete]) and Days([Time Used])>[Deduction Period],(Now()-[Deficiency Deductions Start]),
(not IsNull([Date Complete]) and Days([Time Used])>[Deduction Period]), ([Date Complete]-[Deficiency Deductions Start]))

And finally, my formula for Total Deductions is:
([Time Overdue]*[Deduction])

With the formulas here is what is happening.

Task - Date Identified = 09-19-2018 12:00 AM
Deductions Start = 09-19-2018 01:00 AM
Date Complete = 09-19-2018 02:30 AM
Deduction Period = 1 hour
Time Overdue = 1.5 hours
Deduction = \$1,000
Total Deduction = Formula error says "Expecting Number but found duration"

What I need this to do is show the time overdue as 1.5 hours which it does, but then I need it to take the 1.5 hours & multiply it by \$1000.  But it has to work regardless if the Time Overdue is 1.5 hours or 3 days.  Can someone help me.
• 582 Points
• Frustrated but Optimistic

Posted 7 months ago

• 38
• 4,070 Points
Quick Base has a simple built in function, ToHours(duration), that will convert your time overdue value to a a number that you can solve your conversion issue. So like this -

ToHours([Time Overdue])*[Deduction]

This will work for 1.5 hours and read as 1.5 * 1000, or in the case of 3 days - it will convert to the # of hours (72) and be 72*1000
• 582 Points
Ok that works when my deduction is set up for hours but when I am doing 3 days my deduction is based on days as well.  i.e deduction period is 7 days my deduction is \$5,000 per day (for anything over 7 days).  So I would need the formula to say to take that into consideration.

I am wondering if I could use a case function that says something like:

Case([Deduction Period] contains "Hours", ToHours([Time Overdue]*[Deduction]),ToDays([Time Overdue]*[Deduction]))

But I am not sure how to make the first part of the formula work.
• 4,070 Points
Do you have different values for deductions based on it being hours versus days?

Either way - you're going to want to user an if() statement versus a Case() and check to see if the duration is more than a day and then do your multiplier. Something like this

If( ToDays([Time Overdue]) >= 1,

//Do this if the time overdue is a day or more

ToDays([Time Overdue]) * [Deduction For Days],

//Otherwise do this

ToHours([Time Overdue])*[Deduction For Hours] )
• 582 Points
Awesome!  Your response is much appreciated & definitely helped fix my problem.