Finding the last day of a month 1

[jjkremlin]

I need to show the las day of each month in a report I am doing. Is there a built in function or a piece of code to do this?
Thanks.

 

[synapsevampire]

Try:

date(year(dateadd("m",1,currentdate)),month(dateadd("m",1,currentdate)),1)-1

Replacing currentdate with whatever date you have.

-k

 

[jjkremlin]

I get the following error:

the &quot" is missing

What is wrong?

 

[mbarron]

You may have pasted it incorrectly. The formula is correct as synapsevampire has posted. Mike

 

[synapsevampire]

Try:

date(year(dateadd("m",1,currentdate)), month(dateadd("m",1,currentdate)), 1)-1

I must have missed a paren or something.

-k

 

[jjkremlin]

I am running an older version of crystal ie v7.x.x

It may not be recognizing the dateadd function.

 

[mbarron]

Yep, DateAdd was new in CR 8

Try this one then: (you can get rid of the stuff in BOLD BLUE if you won't be calcating a date before 1901 and after 2099 otherwise the special leap year rules apply

Numbervar mnth:=month({your.date.field});
numbervar yr:=year({your.date.field}
numbervar dayout;
=====================

if mnth in [1,3,5,7,8,10,12] then dayout:=31 else
if mnth in [4,6,9,11] then dayout:=30 else
if remainder(yr,400)=0 then dayout:=29 else
if remainder(yr,100)=0 then dayout:=28 else
if remainder (year,4)=0 then dayout:=29 else
dayout:=28;

date(yr,mnth,dayout)

=====================
Mike

 

[mbarron]

Too quick on the Submit Post. It's all one formula. I put my seperator in the wrong spot. It should have looked like what is posted below:

=====================
Numbervar mnth:=month({your.date.field});
numbervar yr:=year({your.date.field}
numbervar dayout;


if mnth in [1,3,5,7,8,10,12] then dayout:=31 else
if mnth in [4,6,9,11] then dayout:=30 else
if remainder(yr,400)=0 then dayout:=29 else
if remainder(yr,100)=0 then dayout:=28 else
if remainder (year,4)=0 then dayout:=29 else
dayout:=28;

date(yr,mnth,dayout) Mike

 

[chelseatech]

Too dangerous for leap years in 2000 but not in 1900 or 2200. And while our reports may not be running then they will contain dates for those years now, or quite soon.

Try :
numbervar m:=month(currentdate)+1;
//change 1 to move forward or back a month
numbervar y:=year(currentdate);

if m>12 then (m:= m-12; y:=y+1); //Rollover past December
Date(y,m,1) -1

Works well for any years. Change the +1 to +4 and it will be the end of the month in four months time. Change it to -3 and it's 3 months ago.

If you go back a month you might need to add a line to check year rollover prior to Janaury.
if m<1 then (m:= m+12; y:= y -1); Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[goranm]

Hi !

If you want to use synapsvampire´s first suggestion with the DateAdd function
date(year(dateadd(&quot;m&quot;,1,currentdate)), month(dateadd(&quot;m&quot;,1,currentdate)), 1)-1

Then you can download the UFL that handles it.
It will work for Crystal version 7.

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/UFLDateAdd.exe.asp

/Goran

 

[jjkremlin]

Thanks for your wonderful responses. I am still having a problem with the dateadd function. The program recognizes the function but now it says &quot;a date/time required here&quot;.

However the &quot;if then formula&quot; by mbarron improves my solution and I am happy with it.

 

[jjkremlin]

I will take a look at chelseateach's solution also.