Calculate last day of future month

[Danster]

Gday all,

Scenario: I want to find the last day of the month when a customer payment is due based on two criteria i) Number of days (an INT field) ii) a datetime field.

The new date will calculate based on the number of days from the next month, i.e the 1st of the next month, then add the days (assuming 30 means a whole month, so I can't just add the days, I need to to add a whole month).

For example (using dates as dd/mm/yyyy)

Days First Date New Date
30 05/06/2004 31/07/2004
30 20/08/2004 30/09/2004
45 13/08/2004 15/10/2004 or 16/10/2004 (either is OK)
60 05/06/2004 31/08/2004
60 21/11/2004 31/01/2005
75 21/11/2004 15/02/2005 or 16/02/2005 (either)

Is this example clear?

I've already taken a stab at the code, but it became a real mess, so instead of pasting what I've done here and maybe influencing your code, have a go yourself & I can compare.

cheers

Danster

 

[lbass]

I think you could use:

dateadd("d",{table.days}, dateserial(year({table.firstdate}),month({table.firstdate})+1, 01))

-LB

 

[lbass]

I just noticed you referred to the last day of the month, but it looks to me like you are looking for new dates that could be in mid-month as well, and that is what my formula would do.

-LB

 

[Danster]

LB,
Excellent, but one thing to chuck a spanner in the works, when the {table.days} field = 30, it's actually meant as pseudo for the last day of the month, so instead of adding days to the first of next month, I really need it to just display the last day of the month.

eg firstdate newdate
30 5/1/2005 28/2/2005
60 5/1/2005 31/3/2005

else, the code is pretty good and I'll use it until then

 

[lbass]

This might better suit your needs:

if {table.days} = 30 then
dateserial(year({table.date}),month({table.date})+2, 01)-1 else
if {table.days} = 60 then
dateserial(year({table.date}),month({table.date})+3, 01)-1 else
if {table.days} = 45 then
dateserial(year({table.firstdate}),month({table.firstdate})+2, 15) else
if {table.days} = 75 then
dateserial(year({table.firstdate}),month({table.firstdate})+3, 15)

-LB