×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Get Last Day of the Current Month
6

Get Last Day of the Current Month

Get Last Day of the Current Month

(OP)
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

RE: Get Last Day of the Current Month

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

RE: Get Last Day of the Current Month

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

RE: Get Last Day of the Current Month

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.

RE: Get Last Day of the Current Month

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

RE: Get Last Day of the Current Month

2
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

RE: Get Last Day of the Current Month

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

RE: Get Last Day of the Current Month

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.  

RE: Get Last Day of the Current Month

your solution with the variables is much easier.

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

RE: Get Last Day of the Current Month

3
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

RE: Get Last Day of the Current Month

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close