Formula to add 5 days to a date
Hello,
I have a date field called 'invoice due date'. I select the date I want for when I want my invoice to be paid.
I have created another date field called 'Chase date' (not sure if this field should be a formula for what I'm attenting to do).
I would like the 'Chase date' field to automatically select the 'invoice due date' and add 5 days on top of that.
Basically if the invoice is not paid 5 days after the due date I will be able to know that this specific invoice hasn't been paid and so I will be able to chase the client for payment.
Thank you
12 replies
-
Steps:
1. On the Actions icon select Edit Fields.
2. Select the DueDate field
3. Click the Trigger After Update box
4. In the Trigger window write
ChaseDate := DueDate + 5
5. Click Ok to close this window and that's it
Whenever you create or edit the DueDate field the ChaseDate will be filled with a 5 days later date.
Good chasing :)
-
@KS.. You can extend @Jose's recommendation and also add a "Trigger after open" event that pops you into a dashboard.. it sounds like one of your Key Performance Indicators (KPIs) are the number (or pct) of invoices 5 days past due. You can create a dashboard that shows this KPI backed by a view of the selected items.. :)
Yes.. happy chasing..
-
Can you do this across forms?
Does it work with date/time field or only with date field?
I only get a false if I use a formula or nothing if use the above. I am going across forms.
-
Thank you Jose and Mconneen :), it works and will help me chasing late payment :)!
As you seem to be pretty good with Ninox :), could you please help me with another issue please?
My clients have different payment terms.
For example on of my client always pay at the end of the month or another one always pay at the end of the month, on the 5th of the following month.
I'm trying to figure out how to set up my trigger in order to have my 'invoice due date' Date Field automatically field according to the payment terms of my client. I hope this makes sense :) ...
Thank you in advance :)
-
@KS .. add the terms to the client / customer record.. then .. when creating an invoice.. pull the terms from there.
-
Hi Mconneen, thank you I've already done this.
I need to put in place a trigger to automatically calculate the invoice due date based on the date the invoice is created and based accordingly to the customer payment's terms.
Let's say my client's payment terms is 'end of the month, the 5th of the following month'.
If I raise my invoice today, 27th of February my due date for my invoice will be the 5th of March.
I would like to find the right algorythm to calculate it automatically if that makes sense.
-
@KS, It only seems. :)
Mconneen is an expert; I'm still learning Ninox. :)
What you want makes sense but will not be easy to solve as calculating ChaseDate.
It depends on the rules you have (and how many) to calculate due dates for your clients.Suppose you have 3 diferent payment terms, for instance:
A - second friday of the month
B - at the end of the month
C - the fifth day of the next month
Each client will be in one of these categories.
So you will need a column in your table where you would save your client category, say Client_CTG (A, B or C).
You surely have a field with the invoice date Invoice_Date.
In this field, in the Trigger After Update, you would write the code to calculate the due date:
You can use the Switch or if-then statements.Using if it would look something like that:
if Client_CTG = A then
/* here would go calculations for the due date based on your rule
for Client A using Ninox date functions and any other needed */
DueDate := /* output from your calculations */
endif Client_CTG = B then
/* here would go calculations for the due date based on your rule
for Client B using Ninox date functions and any other needed */
DueDate := /* output from your calculations */
endAnd so on for the categories you have for your clients.
-
Did you try [url=https://owlymail.com/faq]temp mail[/url]. It;s free tool.
-
Thank you Jose :)!
yes I know it's not going to be an easy one. I have created a choice field and I have 45 differents payment terms to which I need to find out the right calculation on Ninox... I managed to get this sorted on an Excel document but was hoping to manage to get this sorted on Ninox, better to have it all in one place than having to open Ninox and Excel
-
@KS.. Let us assume that you know due date is the 7th of next month.. unless the 7th is a weekend.. then it is the following Monday.. (I will let you worry about holidays) .. So.. given Feb 27th.. that would be March 9th.. as March 7th is a Saturday..
And the code
From the above.. I will let you tackle the variants. :)
Happy Ninox-ing
-
Thank you very much Mconneen, my apologies for the late reply, I've been working on it the entire week-end... :)
It did help me to figure out few of my payments terms but I'm still struggling with few of them, the main one is 45 days, end of the month, the 5th of the following month.
Do you have any idea on how I could program my database to do something if the invoice date is between the 1st of the month and the 16 and do something else if the invoice date is between the 17th and the end of the month?
I knew this bits would be a pain... many thanks for your help :)
-
@KS
Have you solved the remaining DueDate calculations?
I've been thinking of some ways that could solve your problem, including testing holidays, but I haven't tested them yet.
But if you've already solved them, you're done. :)
Content aside
- 4 yrs agoLast active
- 12Replies
- 3787Views