How to create future date fields that are based a particular date fiel

[merenakk11]

Hello,

I need a bit of help with a Crystal formula.

Basically, assume I have a date – call it KEYDATE (eg. 11 October 2007).

I then need to create future date fields that are based on this date, such that:-

The first date is the end of the KEYDATE month (31 October 2007),

The second date is the end of the next month (30 November 2007)

The third date is the end of the third month (31 December 2007)

The fourth date is the end of the fourth month (31 January 2008)

The fifth date is the end of the fifth month (29 February 2008)

And so on …


Please help,

Thank you,

Tiina

 

[IanWaterman]

One way is do do this

@firstmonth end

Date(Year(dateadd("m", 1,keydate), month(dateadd("m", 1,keydate), 1)-1

2nd monthend
Date(Year(dateadd("m", 2,keydate), month(dateadd("m", 1,keydate), 2)-1

repeat logic for 3rd etc


Each formula returns the fist of the next month and then subtracts one for give you month end. This caters for Feb leap years.

The dateadd function ensures dates switch corectly at year ends.

Ian

 

[merenakk11]

Can’t quite get it to work. There’s a bracket missing, but I can’t tell where.

I also had a go at rewriting the formula (taking the first of the month):-

date(Year({@report date}), month(dateadd("m", 3,{@report date})), 1)

which ‘adds’, in this example, 3 months – but you just end up with 1/1/2007, rather than 1/1/2008.

So I can’t get the year to update automatically like this, and I can’t really tell what your ‘year’ formula is meant to be doing.


Thank you,

Tiina

 

[IanWaterman]

Tina

Sorry my mistake

@firstmonth end

Date(Year(dateadd("m", 1,keydate)), month(dateadd("m", 1,keydate)), 1)-1

2nd monthend
Date(Year(dateadd("m", 2,keydate)), month(dateadd("m", 2,keydate)), 1)-1

3rd monthend
Date(Year(dateadd("m", 3,keydate)), month(dateadd("m", 3,keydate)), 1)-1

Got a bit carried away with changing my 1's to 2's

You have to do this for the yewar
Year(dateadd("m", 2,keydate)) for year precisely for the reason you describe.

Ian

 

[lbass]

If you use dateserial() instead of date(), you do not have to adjust the year or use dateadd for the month--it will automatically adjust, e.g.,

{@thirdmoend}:
Dateserial(Year(keydate), month(keydate)+3, 1)-1

-LB