Discussions

Expand all | Collapse all

Formula to Calculate Amount Due

  • 1.  Formula to Calculate Amount Due

    Posted 11-27-2018 20:50
    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. 


  • 2.  RE: Formula to Calculate Amount Due

    Posted 11-28-2018 04:09
    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


  • 3.  RE: Formula to Calculate Amount Due

    Posted 11-28-2018 14:13
    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.  RE: Formula to Calculate Amount Due

    Posted 11-28-2018 15:14
    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] )


  • 5.  RE: Formula to Calculate Amount Due

    Posted 12-18-2018 14:05
    Awesome!  Your response is much appreciated & definitely helped fix my problem.