Forum Discussion

DangThai's avatar
DangThai
Qrew Trainee
7 years ago

How to Calculate Fees Incurred Daily from [Date Received]

I'm looking for a more elegant formula to calculate fees incurred after a certain date, then daily up to 30 days. Below is what I've put together; is there a shorter way of expressing this?"
"If(ToDays(Today()-[Date Received])=61,1,
ToDays(Today()-[Date Received])=62,2,
ToDays(Today()-[Date Received])=63,3,
ToDays(Today()-[Date Received])=64,4,
ToDays(Today()-[Date Received])=65,5,
ToDays(Today()-[Date Received])=66,6,
ToDays(Today()-[Date Received])=67,7,
ToDays(Today()-[Date Received])=68,8,
ToDays(Today()-[Date Received])=69,9,
ToDays(Today()-[Date Received])=70,10,
ToDays(Today()-[Date Received])=71,11,
ToDays(Today()-[Date Received])=72,12,
ToDays(Today()-[Date Received])=73,13,
ToDays(Today()-[Date Received])=74,14,
ToDays(Today()-[Date Received])=75,15,
ToDays(Today()-[Date Received])=76,16,
ToDays(Today()-[Date Received])=77,17,
ToDays(Today()-[Date Received])=78,18,
ToDays(Today()-[Date Received])=79,19,
ToDays(Today()-[Date Received])=80,20,
ToDays(Today()-[Date Received])=81,21,
ToDays(Today()-[Date Received])=82,22,
ToDays(Today()-[Date Received])=83,23,
ToDays(Today()-[Date Received])=84,24,
ToDays(Today()-[Date Received])=85,25,
ToDays(Today()-[Date Received])=86,26,
ToDays(Today()-[Date Received])=87,27,
ToDays(Today()-[Date Received])=88,28,
ToDays(Today()-[Date Received])=89,29,
ToDays(Today()-[Date Received])>=90,30,
null)"
Formulas and functions
  • DangThai's avatar
    DangThai
    Qrew Trainee
    7 years ago
    This reply was created from a merged topic originally titled How to Calculate Fees Incurred Daily from [Date Received].

    I'm looking for a more elegant formula to calculate fees incurred after a certain date, then daily up to 30 days. Below is what I've put together; is there a shorter way of expressing this?
    f(ToDays(Today()-[Date Received])=61,1,
    ToDays(Today()-[Date Received])=62,2,
    ToDays(Today()-[Date Received])=63,3,
    ToDays(Today()-[Date Received])=64,4,
    ToDays(Today()-[Date Received])=65,5,
    ToDays(Today()-[Date Received])=66,6,
    ToDays(Today()-[Date Received])=67,7,
    ToDays(Today()-[Date Received])=68,8,
    ToDays(Today()-[Date Received])=69,9,
    ToDays(Today()-[Date Received])=70,10,
    ToDays(Today()-[Date Received])=71,11,
    ToDays(Today()-[Date Received])=72,12,
    ToDays(Today()-[Date Received])=73,13,
    ToDays(Today()-[Date Received])=74,14,
    ToDays(Today()-[Date Received])=75,15,
    ToDays(Today()-[Date Received])=76,16,
    ToDays(Today()-[Date Received])=77,17,
    ToDays(Today()-[Date Received])=78,18,
    ToDays(Today()-[Date Received])=79,19,
    ToDays(Today()-[Date Received])=80,20,
    ToDays(Today()-[Date Received])=81,21,
    ToDays(Today()-[Date Received])=82,22,
    ToDays(Today()-[Date Received])=83,23,
    ToDays(Today()-[Date Received])=84,24,
    ToDays(Today()-[Date Received])=85,25,
    ToDays(Today()-[Date Received])=86,26,
    ToDays(Today()-[Date Received])=87,27,
    ToDays(Today()-[Date Received])=88,28,
    ToDays(Today()-[Date Received])=89,29,
    ToDays(Today()-[Date Received])>=90,30,
    null)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    Try this

    var number DaysOverDue = ToDays(Today()-[Date Received];

    If($DaysOverDue > 60, $DaysOverDue - 60)
    • DangThai's avatar
      DangThai
      Qrew Trainee
      7 years ago
      Thank you but it goes on indefinitely. The fee should start at 60 days after date received, incurring $1 a day up to a max of 30 days (90 days after date received)?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      No problem

      var number DaysOverDue = Min(30, ToDays(Today()-[Date Received]);

      If($DaysOverDue > 60, $DaysOverDue - 60)

      // the formula above will use the smaller of 30 and the # of days overdue.
    • DangThai's avatar
      DangThai
      Qrew Trainee
      7 years ago
      Sorry, it's not giving me the calculation I'm looking for. Even added the ) to the
      var number DaysOverDue = Min(30, ToDays(Today()-[Date Received])); 

      If($DaysOverDue > 60, $DaysOverDue - 60)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I believe You still need the IF or else the days overdue will be initially negative.