Sorting a date range as string

Question

Hello,
I have a summary report where depending on their completion date will get grouped together by the work week they're supposed to be in but im having issues trying to sort them properly since the days are text.

They show up like this. I would very much like these sorted from oldest to newest. Any ideas? The choices for the custom sorting didnt really give me a lot
------------------------------Tim D------------------------------

markshnier__you · Answer

you will need to create a new formula text field to identify the week in the format&nbsp;
YYYY-MM-DD to YYYY-MM-DD------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

timd · Answer

Mark, I dont seem to be following.
How will that fix the sorting issue? Wouldn't that still run into the same issue?------------------------------Tim D------------------------------

markshnier__you · Answer

I believe that your field for "week" is actually a formula text field type.&nbsp; Is that right?
So the report is just an alpha sort.
If it is a text formula field then if you post your formula that I can help you rewrite it into a way that would sort alpha correctly.&nbsp;&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

timd · Answer

Thanks Mark!This is what I have

var date start = If(DayOfWeek(ToDate([ppcd_formula]))=6,ToDate([ppcd_formula]), PrevDayOfWeek(ToDate([ppcd_formula]), 6));
var date end = If(DayOfWeek(ToDate([ppcd_formula]))=5, ToDate([ppcd_formula]),
NextDayOfWeek(ToDate([ppcd_formula]), 5));

ToText($start) &amp; " - " &amp; ToText($end)------------------------------Tim D------------------------------

markshnier__you · Answer

ok try this
var date start = If(DayOfWeek(ToDate([ppcd_formula]))=6,ToDate([ppcd_formula]), PrevDayOfWeek(ToDate([ppcd_formula]), 6));
var date end = If(DayOfWeek(ToDate([ppcd_formula]))=5, ToDate([ppcd_formula]),
NextDayOfWeek(ToDate([ppcd_formula]), 5));

// the text above does not change.
// then followed by this .....
var text StartYYYYMMDD =
List("-", ToText(Year($Start)),
Right("0" &amp; Month($Start),2),
Right("0" &amp; Month($Start),2));
&nbsp;
var text EndYYYYMMDD =
List("-", ToText(Year($End)),
Right("0" &amp; Month($End),2),
Right("0" &amp; Month($End),2));

List(" to ", $StartYYYYMMDD, $EndYYYYMMDD)------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Sorting a date range as string