# Crystal: Date Formula

Hello:

I don't have access to my Crystal report, at the moment.  But, attached is the Excel version that it resembles.

In the upper left-hand corner, you will notice the phrase "2nd 2014 Carbon Commissions" and that it relates to the date on the line below it.

What formula in Crystal do I use to get the report to display "2nd 2014 Carbon Commissions" or even "1st 2014 Carbon Commissions" for the first-half of the year?  And, if the line below it containing dates doesn't have contiguous dating, is there a way to get this second line to perhaps not display anything?

Thanks!

TBSupport
Copy-of-Carbon-Commission-Crystal-report
LVL 1
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
It is a calculation/formula that uses a date as input and converts it into quarterly breaks.
Author Commented:
Thanks, arnold!  Can you give me a general idea as to how that is accomplished?

TBSupport
Commented:
Try this

If ({YourDateFIeld} <  Date(Year(YourDateField), 7,1) then
"First" + CStr(Year(YourDateField),0,'') + "Carbon Commissions"
Else
"Second " + CStr(Year(YourDateField),0,'') + "Carbon Commissions"

mlmcc
Commented:
That is a good example of breaking the year into halves.

The grouping of the data is an additional step.
A mathematical formula dealing with date and Month of year and using that to break the year into portions.
Author Commented:
Hi mlmcc:

With this formula, I get a message saying that the ) is missing:

If ({@Date Parameter Heading} <  Date(Year(@Date Parameter Heading), 7,1) then
"First" + CStr(Year(@Date Parameter Heading),0,'') + "Carbon Commissions"
Else
"Second " + CStr(Year(@Date Parameter Heading),0,'') + "Carbon Commissions"

TBSupport
Commented:
You are missing a closing ) on the expression,  add ')' before the word then
Author Commented:
Hi Arnold:

I still get the same error.

TBSupport
Commented:
Corrected formula

``````If ({YourDateFIeld} <  Date(Year(YourDateField), 7,1)) then
"First" + CStr(Year(YourDateField),0,'') + "Carbon Commissions"
Else
"Second " + CStr(Year(YourDateField),0,'')) + "Carbon Commissions"
``````

mlmcc
Author Commented:
Hi mlmcc:

I still get the same error.

TBSupport
Commented:
Please post what you have, and the error you get. Text of the error is preferable to images of the error message.
The issue might be that the field on which you act is of the wrong type.
Commented:
Still have it wrong

``````If ({YourDateFIeld} <  Date(Year({YourDateField}), 7,1)) then
"First" + CStr(Year({YourDateField}),0,'') + " Carbon Commissions"
Else
"Second " + CStr(Year({YourDateField}),0,'') + " Carbon Commissions"
``````

If that doesn't work (make sure you have replaced {YourDateField} with the date field in your data), please post a screenshot of the code or copy and paste the code here.

mlmcc
Author Commented:
Attached is the code and the error message that I get.
Doc4.docx
Commented:
You have double {{ in front of the name.  Change them to {.

All 3 lines have that

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Below is my latest code.  On the second "@Date Parameter Heading", I get a message saying that "A date is required here".

If ({@Date Parameter Heading}) <  Date(Year({@Date Parameter Heading}), 7,1) then
"First" + CStr(Year({@Date Parameter Heading}),0,'') + " Carbon Commissions"
Else
"Second " + CStr(Year({@Date Parameter Heading}),0,'') + " Carbon Commissions"
Commented:
Apparently your formula doesn't return a date.

What is the @Date Parameter Heading code or if It is a parameter what type is it?

mlmcc
Author Commented:
Hi mlmcc:

@Date Parameter Heading is the following:

WhilePrintingRecords;

DateTimeVar date1;
DateTimeVar date2;

date1:=Minimum({?Dates});
date2:=Maximum({?Dates});

totext(Date(date1)) + " thru " + totext(Date(date2))

It allows me to tell the end user what the range of {?Dates} is.

TBSupport
Commented:
That is a string.

Since you have a date range, do you want to use the beginning or ending date?

Change your formula to

``````WhilePrintingRecords;

DateTimeVar date1;
If (date1) <  Date(Year(date1), 7,1) then
"First" + CStr(Year(date1),0,'') + " Carbon Commissions"
Else
"Second " + CStr(Year(date1),0,'') + " Carbon Commissions"
``````

mlmcc
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.