Changing Format of the Date

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
Babypink2807Asked:
Who is Participating?
 
JayConverseConnect With a Mentor Commented:
To get rid of the decimals and commas, use

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

Better yet, use this by itself:

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





0
 
JayConverseCommented:
Right click on the formula, you can format it any way you like.
0
 
Babypink2807Author Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
JayConverseCommented:
That's right, select Format Field.  Keep going...
0
 
JayConverseCommented:
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
 
JayConverseCommented:
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
 
Babypink2807Author Commented:
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
 
Babypink2807Author Commented:
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
 
Babypink2807Author Commented:
My cross tab is set up as


Cross-Tab-Setup.bmp
0
 
JayConverseCommented:
I mean:

ToText({InvHdr.DATE}),"MMM/yyyy")
0
 
Babypink2807Author Commented:
where do I enter

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

I am so sorry to be thick here
0
 
JayConverseCommented:
Crosstab Expert/Group Options/Options/Use a formula as GroupName
0
 
Babypink2807Author Commented:
Oh my god, ive done it!

Whoop Whoop

Thank you so much you are brilliant

Sorry to have been a pain

:-)
0
 
JayConverseCommented:
Goodness, I took the time to help, I figure I should get a little credit.
0
 
Babypink2807Author Commented:
Your all very patient and superb, but I have marked you as an "A"
0
 
WhackAModCommented:
Starting closing process on behalf of the asker.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.