Solved

Changing Format of the Date

Posted on 2011-03-18
17
246 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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