Crystal Reports 2011 (need help with date formula)

I want to create a formula that will do the following:

If the data in my database has a date = to current month then name of month (i.e. March)

If the data in my database has a date = previous month then name of month (i.e. Feb)

And so on so that I can build a rolling 13 month view that the user will get without having to enter any parameter..
Who is Participating?

Improve company productivity with a Business Account.Sign Up

mlmccConnect With a Mentor Commented:
If you mean you need Feb 1 2011 then

{yourDateField} >= DateSerial(Year(CurrentDate)-1, Month(CurrentDate)-1,1)

If you need for today 21 Feb, 2011

{yourDateField}  >= DateAdd('m',-13,CurrentDate)

I assume you are trying to get the previous 13 months so for today you want Mar 1 2011 and later

{yourDateField} >= Date(Year(CurrentDate)-1, Month(CurrentDate),1)

MonthName (Month ({Your Date Field}))
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

wshcraft70Author Commented:
Actually rolling..  Current plus previous 12 so I've got a rolling 13 month display from current date..

So how would I do that since my year will change to 2011 since we're currently only in March?
wshcraft70Author Commented:
I'm thinking I'm going to need some sort of IF statement somewhere but....

So if user opens report and the MONTH/YEAR is AUGUST 2012 for instance then I would want:

JULY 2012
JUNE 2012
MAY 2012
APRIL 2012
MARCH 2012
FEB 2012
JAN 2012
DEC 2011  ****
NOV 2011
OCT 2011
SEPT 2011
AUG 2011

I've created a section for each month in the Group Footer as I have several more formuals to calculate..

What I don't want to user to have to do is enter any dates..  I want the system to do it's thing..  But what I don't know how to do is how to create a formula that will take into account the previous year when applicable to that I have a clean 13 months consecutively..  Am I over complicating this again?
Have you tried any of my formulas in the report select expert?

Is the problem selecting the data or in having the labels correct?

wshcraft70Author Commented:
The problem is me..  haha!!

But I've managed to get the labels working using your suggestions above..  

I was hoping I was going to be able to work out the next step with that but turns out I'm a bit OFF..    Am happy to SCORE and put this out for another solution..

Now what I'm trying to fix is the FORMULA named Current Date so I can return the data from my database where FIELD {dates} is = to current month else 0

I've tried this just as a PURE GUESS and nothing more..  There is CLEARLY something wrong with it..  Thoughts?

if Month({NC_Call_Report_Stats.Dates}) = (Month(DateAdd ("m", 0, CurrentDate))) and  {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})
wshcraft70Author Commented:
I think I'm on to something..  

if Month({NC_Call_Report_Stats.Dates}) = Month(DateAdd ("m", -1, CurrentDate)) and  
year({NC_Call_Report_Stats.Dates}) = Year(DateAdd ("y", 0, Currentdate))
and {NC_Call_Report_Stats.GroupID} = {?Group}
then ({NC_Call_Report_Stats.BaseID})
wshcraft70Author Commented:
Thank you for everything... You are AWESOME!!!  Thanks for your patience with me..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.