Forum Discussion

TimD's avatar
TimD
Qrew Trainee
3 months ago

Sorting a date range as string

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 ...
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion

you will need to create a new formula text field to identify the week in the format 

YYYY-MM-DD to YYYY-MM-DD



------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
TimD's avatar
TimD
Qrew Trainee
3 months ago

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's avatar
    MarkShnier__You
    Qrew Champion
    3 months ago

    I believe that your field for "week" is actually a formula text field type.  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.  



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • TimD's avatar
      TimD
      Qrew Trainee
      3 months ago

      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) & " - " & ToText($end)


      ------------------------------
      Tim D
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 months ago

        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" & Month($Start),2),
        Right("0" & Month($Start),2));
         
        var text EndYYYYMMDD =
        List("-", ToText(Year($End)),
        Right("0" & Month($End),2),
        Right("0" & Month($End),2));
        List(" to ", $StartYYYYMMDD, $EndYYYYMMDD)


        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------