Solved

Changing Format of the Date

Posted on 2011-03-18
17
250 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 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