Get Last Day of the Current Month 6

[75cl]

Is there a way that you can last day of the current month in crystal report. For example user enter 2001/10/25
So the last day of this month is 2001/10/31. Any function in crystal report will calculate this. Thank You in Advance

 

[DjangMan]

I've created a UFL that contains that function if you're interested. It means installing on the machines that will use this report. Let me know if you want it (free).

DjangMan

 

[dgillz]

Here's the formula:

@lastday
//this part handles leap year Februarys
If Remainder(year(Currentdate),4)=0 and Month(Currentdate)=2 then Date(Year(Currentdate),Month(Currentdate),29)

//handles February if not a leap year
else if month(Currentdate)=2 then Date(Year(Currentdate),Month(Currentdate),28)

//Handles April, June, September and November ... remember the rhyme, 30 days hast September????
else if month(Currentdate) in [4,6,9,11] then Date(Year(Currentdate),Month(Currentdate),30)

//Handles All the rest
else Date(Year(Currentdate),Month(Currentdate),31)

disclaimer.....as a Astronomy minor (a long time ago) I remember that every 400 years, there is a February 30th. I do not remember when the next Feb 30th is, but this formula will not work for that month!!!!! Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[csm2]

dgillz
You did not take into account that century years not divisible by 400 are not leap years. Even though they are divisible by 4.

 

[dgillz]

Csm2
I have never heard of this before. The year 2000 is divisible by 400 and it was a leap year. I did a bit of research on the net as to when Feb 30th actually comes into play, but I have gotten mixed results. I'll leave this one to someone who cares. Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[rhinok]

Here's a slightly different version of the formula that takes into account years divisible by 400. The formula also uses variables which makes it a bit cleaner to read and interpret:

// @LastDay
// This formula defines Last Day of the Current Month
NumberVar MonthVar;
NumberVar YearVar;
NumberVar DayVar;
DateVar CurrentLastDay;

// Defines the Current Month Variable
MonthVar := Month(CurrentDate);

// Defines the Current Year Variable
YearVar := Year(CurrentDate);

// Defines the Last Day Variable
If
MonthVar in [1,3,5,7,8,10,12]
Then
DayVar:= 31
Else If
MonthVar in [4,6,9,11]
Then DayVar:= 30
Else If
MonthVar = 2 and
(Remainder(YearVar,4) = 0 and
Remainder(YearVar,100) <> 0) or
Remainder(YearVar,400) = 0
Then
DayVar:= 29
Else
DayVar:= 28;

// Defines the Current Last Day Date Variable
CurrentLastDay:= Date(YearVar, MonthVar, DayVar);

// Returns the Last Day of the Current Month
CurrentLastDay

 

[75cl]

Thank You So Much For all your help, everyone. I am really appreciated for such a prompt response.
Thanks again,
CL

 

[csm2]

dgillz
most programmers are familiar with the 'divisible by 4' test for leap years. However, far fewer are familiar with the 'century divisible by 400' test (i.e. 1700,1800,1900 were not leap years). I believe there is a further part to the 'leap year' test but the situation occurs so infrequently that it may be discounted.

 

[dgillz]

your solution with the variables is much easier. Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[kenhamady]

Here is another variation. It requires the DateAdd function that comes with V8, but you can downlowad it for older versions. It lets the Crystal calendar deal with the leap years:


WhileReadingRecords;
DateVar Last:=
DateTimetoDate (DateAdd( 'm', 1 , {Orders.Order Date} ));
Last - Day(Last) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Ken,

I did not know you could do that. Where do you go to download new functions such as dateadd so they can be used with a crystal V7 installation?

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

It was here last time I checked:

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

I have never tried using it with Info, so let me know if it works. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com