asked on

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

JayConverse

Right click on the formula, you can format it any way you like.

Babypink2807

ASKER

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?

JayConverse

That's right, select Format Field. Keep going...

JayConverse

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.

JayConverse

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

Babypink2807

ASKER

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

Babypink2807

ASKER

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

Babypink2807

ASKER

My cross tab is set up as

Cross-Tab-Setup.bmp

ASKER CERTIFIED SOLUTION

JayConverse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JayConverse

I mean:

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

Babypink2807