Oyseka Posted August 4, 2011 Posted August 4, 2011 Hi All, I want to calculate the due date of payment for products received based on the supplier terms. All of the terms are based on a period of calendar months following statement date. so I have the following: Date Received, this is a calculation field based on the product type i.e. if retail then it is the actual date received or if consignment then it is the date sold. Product Terms, this is a lookup from the products table and is in months i.e. 1 or 2 or 3 Due Date which is where I want to calculate the date. what I tried is this Date (Month ( Date_Rec )+Terms;0;Year(Date_Rec)) which is a total ********. I need to return the last day of the month such that if a product is "Received" on the 15/11/2010 and the terms are 2 the due date returns 28/02/2011. Thank you
comment Posted August 4, 2011 Posted August 4, 2011 I need to return the last day of the month such that if a product is "Received" on the 15/11/2010 and the terms are 2 the due date returns 28/02/2011. Can you explain the logic here? Two months from 15/11/2010 is 15/1/2011, and the last day of the month is 31/1/2011. Where did the extra month come from?
Oyseka Posted August 4, 2011 Author Posted August 4, 2011 Can you explain the logic here? Two months from 15/11/2010 is 15/1/2011, and the last day of the month is 31/1/2011. Where did the extra month come from? Hi Comment, The two months are based on Statement date which is the last day of the month so effectively we could get very nearly three months (from the 1/11/2010 to the last day of the month, two months after the end of that month. that is why I have to work on months rather than days from invoice. Edited for abysmal english
comment Posted August 4, 2011 Posted August 4, 2011 we could get very nearly three months (from the 1/11/2010 to the last day of the month, two months after the end of that month. That's still 31/1/2011, isn't it? With a result of 28/2/2011 you'd be close to four months.
Oyseka Posted August 4, 2011 Author Posted August 4, 2011 Whoops, one month more would be good but no, you are correct.
comment Posted August 4, 2011 Posted August 4, 2011 Well, then: Date ( Month ( Date_Rec ) + Terms + 1 ; 0 ; Year( Date_Rec ) ) should work for you.
Oyseka Posted August 4, 2011 Author Posted August 4, 2011 How on earth did I miss the +1 (Embarrassment) Thank you
Oyseka Posted August 4, 2011 Author Posted August 4, 2011 Whoa, Still not working. Copy of field calc Date (Month ( Date_Rec )+Products::Terms+1;0;Year(Date_Rec)) Field settings are Unstored, Calculation result Date, Evaluate always. (It won't stay like that I just changed it to try and get it to work) I have a Date_Rec 24/07/2011 and a Due_Date 31/07/2011 with the terms set at 2 (See Screenshot)
comment Posted August 4, 2011 Posted August 4, 2011 Add the field Products::Terms to the layout and see what you get.
Oyseka Posted August 4, 2011 Author Posted August 4, 2011 Weird stuff. The values of the Terms field make the calculation correct (the Terms field on the first line of the screenshot for instance is blank) but the product term for that product says 3 ?
Recommended Posts
This topic is 4868 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now