Date formula change to numeric format

[cjbrown815]

Hello,
I am currently using the formula below to combine two sets of data for sorting in a line graph. The problem is, the sort is messed up when multiple months are displayed


apr 01 2010
apr 02 2010
mar 01 2010
mar 02 2010

I need them to sort in order by date. I thought if I can alter this formula to display 2010_0423 instead of APR 23 2010, It would be easier to sort.

Formula:
ToText({SAMPLE.DATE1}, "MMM/dd/yyyy")& " " & " " & {SAMPLE.TEXT3}


thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[fisheromacse]

The below formula should display the format you requested.

//{@DateYearFirst}
year({SAMPLE.DATE1})&"_"&month({SAMPLE.DATE1})&day({SAMPLE.DATE1})

 

[IanWaterman]

If its a date field you can just sort on it and all will be OK.

If its a string then convert to date before doing fisheroacse formula

//{@DateYearFirst}
year(datevalue({SAMPLE.DATE1}))&"_"&month(datevalue({SAMPLE.DATE1}))&day(datevalue({SAMPLE.DATE1}))

Ian