Forum Discussion

AmberPolston's avatar
AmberPolston
Qrew Cadet
7 years ago

Formula to Calculate Amount Due

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
  • If(IsNull([Date Complete]),ToDays(Now()-[Task - Date Identified]),ToDays([Date Complete]-[Task - Date Identified]))

  • [% 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:
If(ToText([Task - Date Identified])<>"",([Task - Date Identified]+[Deduction Period]))

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. 
Formulas and functions
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    7 years ago
    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
  • AmberPolston's avatar
    AmberPolston
    Qrew Cadet
    7 years ago
    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.
  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    7 years ago
    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] )
  • AmberPolston's avatar
    AmberPolston
    Qrew Cadet
    7 years ago
    Awesome!  Your response is much appreciated & definitely helped fix my problem.