0

Date functions & calculations

Next question! I've got a situation where I want to indicate when a regular payment or bill happens. Sometimes ths can be framed as "the 2nd Wednesday of each month" and sometimes as "the 1st (15th, etc) of each month". I don't want to iuse a specific date which would include a year value, instead I want Ninox to know it's (for example) the 1st of the next month, whatever that is. And then be able to calculate the next due date from today's date. It seems to me there must be a way to do this, I can't figure out how. 

There are also some expenses that are billed once a year, or once every 4 years (for example, the fee to renew a drivers license), the ones that are monthly are my concern at the moment.

I have learned how to break down a date into 3 separate number fields, and do some date calculations with that information, but that doesn't seem to directly apply to the more general case. 

TIA for any advice on this. 

4 replies

null
    • zenmom
    • 2 yrs ago
    • Reported - view

    P.S. Is there any way to go back and edit a post? I see that I've left out a word in a couple places.

    • Fred
    • 2 yrs ago
    • Reported - view

    Hi zenmom -

     

    Sadly, there is no way to edit posts. Just post your corrections/additions.

     

    Here is one option. See if this is what you want:

     

    1) I created a choice field and called it PaymentSchedule. Then add three choices (1st of Month, 15th of Month, Yearly).

     

    2) Then I created a formula field called nextPayDue and put this in it:

     

    switch PaymentSchedule do
    case 1:
    (
    let xMon := month(today());
    let nxtDate := date(year(today()), xMon + 1, 1);
    let xD := days(today(), nxtDate);
    if xD < 14 then
    "Pay Now"
    else
    "Pay in " + xD + " days"
    end
    )
    case 2:
    (
    let xMon := month(today());
    let nxtDate := date(year(today()), xMon + 1, 15);
    let xD := days(today(), nxtDate);
    if xD < 14 then
    "Pay Now"
    else
    "Pay in " + xD + " days"
    end
    )
    case 3:
    (
    let xYr := year(today());
    let nxtDate := date(xYr + 1, month(today()), month(today()));
    let xD := days(today(), nxtDate);
    if xD < 365 then
    "Pay in " + xD + " days"
    else
    "A long way to go"
    end
    )
    end

     

    What this does is use the switch command that takes the PaymentSchedule choice and does a calculation based on what is selected.

     

    So to break down the code of case 1:

     

    let xMon := month(today());
    let nxtDate := date(year(today()), xMon + 1, 1);
    let xD := days(today(), nxtDate);
    if xD < 14 then
    "Pay Now"
    else
    "Pay in " + xD + " days"
    end

     

    Since I need to know the next 1st of the month I first have to find out what month we are currently in, so I created the xMon variable.

     

    Then I use the date command in the nxtDate variable to create a date that is the 1st of next month. You can probably combine the first two lines but this works for my brain.

     

    Then I use the days command to find out how manys days from today to nxtDate.

     

    The next line, I use an if statement to see if the number of days is less than 14. If it is then the field says Pay Now. Otherwise it tells me how many days I have to pay it.

     

    This gets repeated if I select the 15th of the Month, but with slight modification.

     

    Then for yearly I have to change things around because I have to look at the year, but that should be clear.

     

    I hope this helps.

    • Fred
    • 2 yrs ago
    • Reported - view

    So it just dawned on me that the yearly code will not help you out as it will always compute a date a year from now.

     

    What you need for yearly payments, is a month and day you need to make the yearly payment by. You can create a new date field, call it YearlyMonth. Then you would select a date in that field.

     

    Then you would modify case 3 to be:

     

    let xYr := year(today());
    let nxtDate := date(xYr + 1, month(YearlyMonth), day(YearMonth);

     

    This way you will always have a hard month and day that is next year to compare today to.

     

    Also I noticed that my yearly code had two months. You can guess what should go at the end.

    • zenmom
    • 2 yrs ago
    • Reported - view

    Thanks! I'll give this a try.

Content aside

  • 2 yrs agoLast active
  • 4Replies
  • 476Views