number to date - ordering and charting

[mart10]

I am using CR 2016 SP2

The dates in the SQL db I have are stored as numbers I convert as follows:

totext({View_MM_LT_Deals_U_AccountBalances_V3.StartDate} + Date(1979, 12, 31)) tis will show when I format to 20-Feb-18 for instance

I had 2 issues - sorting the data by date AND charting the data by date

1. Sorting by date I resolved by grouping by the date NUMBER and then by group "date" formula - I then supressed the first group and the presentation of raw data looks fine in correct date order

2. Where I am stuck is charting - where the date is the X axis. If I use the date NUMBER then it presents the data in correct order BUT shows the number and not the date format I need. If I present by using the "date" formula then it looks good but it doesnt show in the real date order

 

[IanWaterman]

If you leave your date as a date then sort order will be fine.
Remove the Totext() wrapper from your formula.
You can format display of date in Crystal to achieve the format you want as 20-Feb-18 just right click field on designer select date format and then click custom and set as you want.
Ian

 

[mart10]

cheers worked fine