How to Calculate Fees Incurred Daily from [Date Received]

  • 1
  • 1
  • Question
  • Updated 3 months ago
  • Answered
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)"
Photo of Dang Thai

Dang Thai

  • 172 Points 100 badge 2x thumb

Posted 3 months ago

  • 1
  • 1
Photo of Dang Thai

Dang Thai

  • 172 Points 100 badge 2x thumb
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)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Try this

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

If($DaysOverDue > 60, $DaysOverDue - 60)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
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.
Photo of Dang Thai

Dang Thai

  • 172 Points 100 badge 2x thumb
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)
Photo of Dang Thai

Dang Thai

  • 172 Points 100 badge 2x thumb
made it work
var number DaysOverDue = Min(90, ToDays(Today()-[Date Received])); 
If($DaysOverDue > 60, $DaysOverDue - 60)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
OK, Very good.
(Edited)
Photo of Dang Thai

Dang Thai

  • 172 Points 100 badge 2x thumb
Thank you for your help good sir!
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
No If() needed:
ToDays(Today()-[Date Received]) - 60
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
I believe You still need the IF or else the days overdue will be initially negative.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
You are correct.