[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Changing Format of the Date

Posted on 2011-03-18
17
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 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