Date Calculation (Again)

[Oyseka]

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]

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]

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]

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]

Whoops, one month more would be good but no, you are correct.

[comment]

Well, then:

Date ( Month ( Date_Rec ) + Terms + 1 ; 0 ; Year( Date_Rec ) )

should work for you.

[Oyseka]

How on earth did I miss the +1

(Embarrassment)

Thank you

[Oyseka]

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]

Add the field Products::Terms to the layout and see what you get.

[Oyseka]

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 ?