Discussions

Expand all | Collapse all

How to Calculate Fees Incurred Daily from [Date Received]

  • 1.  How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 18:12
    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)"


  • 2.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 18:32
    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)


  • 3.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 18:38
    Try this

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

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


  • 4.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 19:03
    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)?


  • 5.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 19:09
    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.


  • 6.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 19:22
    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)


  • 7.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 19:23
    made it work
    var number DaysOverDue = Min(90, ToDays(Today()-[Date Received])); 
    If($DaysOverDue > 60, $DaysOverDue - 60)


  • 8.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 19:24
    OK, Very good.


  • 9.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 19:29
    Thank you for your help good sir!


  • 10.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Top
    Contributor
    Posted 09-06-2018 18:40
    No If() needed:
    ToDays(Today()-[Date Received]) - 60


  • 11.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Posted 09-06-2018 18:45
    I believe You still need the IF or else the days overdue will be initially negative.


  • 12.  RE: How to Calculate Fees Incurred Daily from [Date Received]

    Top
    Contributor
    Posted 09-06-2018 18:51
    You are correct.