Solved

Changing Format of the Date

Posted on 2011-03-18
17
240 Views
Last Modified: 2012-05-11
Hi I have created a report (sort of) that shows sales revenue over a given period
 by Sales person using Cross Tab Wizard

Originally the date comes through for everyday and I changed it to Grouping Monthly through
the Cross Tab Expert area.  I then needed to change it to show the Month using a formula
month({InvHdr.DATE}) which shows just the month, what I want it to show is the Month
and Year? so it displays as Apr 2011 how can I change the format please.  

Sorry about the dumb question but I have only beenusing Crystal for a week :-(

Thanks

Julie
0
Comment
Question by:Babypink2807
  • 8
  • 7
17 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164277
Right click on the formula, you can format it any way you like.
0
 

Author Comment

by:Babypink2807
ID: 35164295
I have and I only have the window that shows Format Field and there is no facility in there to change the format of the date?
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164311
That's right, select Format Field.  Keep going...
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164322
Wait, you're formatting Month({InvHdr.DATE})?  That's an integer now.  Paste just {InvHdr.DATE} where you want to see it, then you can format the date.
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164396
Here's another idea.  Go to Crosstab Expert/Group Options/Options/Use a formula as GroupName, and you can set it like

Totext(Month({InvHdr.DATE})) + "/" + totext(Year({InvHdr.DATE}))
0
 

Author Comment

by:Babypink2807
ID: 35164401
Initially my dates all come through as 21/02/2011 as daily, I then Group them by month (under
Cross Tab Expert.  and it comes through as 1, 2, 3 4 etc

Sorry, first formula is Month({InvHdr.DATE})?  then I created a second formula MONTHNAME({@MONTH},TRUE) to change from a numeric ie 1, 2, 3 in to a name ie Jan, Feb, Mar

What I then realised was it only shows the month name in Apr, May, Jun what I need it to show is Apr 2011 and Jun 2011

Sorry
0
 

Author Comment

by:Babypink2807
ID: 35164562
Ok, I have started again with a brand new document and the cross tab is set up with:

ROWS:  Sales Name
SUMMARISED FIELDS:  Qty Goods (ie sum for the month of sales)
COLUMNS:  Invdr Date (which is invoice date)

Ive gone into Group Options as detailed above and put in the formula under option as above and
it now displays as




Crystal-report-Date-Format.bmp
0
 

Author Comment

by:Babypink2807
ID: 35164572
My cross tab is set up as


Cross-Tab-Setup.bmp
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Accepted Solution

by:
JayConverse earned 500 total points
ID: 35164618
To get rid of the decimals and commas, use

ToText({number}),0,"")

Better yet, use this by itself:

ToText({InvHdr.DATE}),"MMM yyyy")





0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164624
I mean:

ToText({InvHdr.DATE}),"MMM/yyyy")
0
 

Author Comment

by:Babypink2807
ID: 35164634
where do I enter

ToText({InvHdr.DATE}),"MMM yyyy")

I am so sorry to be thick here
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164640
Crosstab Expert/Group Options/Options/Use a formula as GroupName
0
 

Author Comment

by:Babypink2807
ID: 35164659
Oh my god, ive done it!

Whoop Whoop

Thank you so much you are brilliant

Sorry to have been a pain

:-)
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 35164715
Goodness, I took the time to help, I figure I should get a little credit.
0
 

Author Comment

by:Babypink2807
ID: 35164758
Your all very patient and superb, but I have marked you as an "A"
0
 

Expert Comment

by:WhackAMod
ID: 35213350
Starting closing process on behalf of the asker.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now