Help with Dates

[pkelly]

Looking for some help on dates...

I have a field called "pdr_DueDateBasis" which, depending on the receipt status, is either our actual receipt date or an estimated receipt date for goods in transit to us.

Using my vendor business terms table, I am trying to work out when payment is due based on this field.

The scenario (of which there are many) I am working on revolves around two further fields...

bus_ProxyDiscountDayOfMonth e.g. 15

bus_ProxyDiscountMonthPlus e.g. 2

So if I had a receipt date of 04/02/2011 (dd/mm/yyyy format) my payment due date using the above example figures would be the 15/04/2011.

My "If" statement to try and construct this date in my script is as follows...

*** Code Starts ***

IF(bus_Proxy = 'Yes' AND bus_ProxyDiscountFixedDate = 'Blank' AND bus_ProxyDiscountDayOfMonth <> 31,

Date(

// Day

bus_ProxyDiscountDayOfMonth & '/' &

// Month and Year

addmonths(Date(pdr_DateDueBasis, 'MM/YYYY'), bus_ProxyDiscountMonthPlus)

, 'DD/MM/YYYY')

*** Code Ends ***

Any assitance greatly appreciated...

Regards

Paul

[pkelly]

Is make date a better approach for this?

[pkelly · Accepted solution]

Finally got it....

Used...

IF(bus_Proxy = 'Yes' AND bus_ProxyDiscountFixedDate = 'Blank' AND bus_ProxyDiscountDayOfMonth <> 31,
makedate(
Year(addmonths(Date(pdr_DateDueBasis, 'DD/MM/YYYY'), bus_ProxyDiscountMonthPlus)),
Month(addmonths(Date(pdr_DateDueBasis, 'DD/MM/YYYY'), bus_ProxyDiscountMonthPlus)),
bus_ProxyDiscountDayOfMonth)