Forum Discussion

KorrieClark's avatar
KorrieClark
Qrew Trainee
3 months ago

Formula Date > Text field

Looking for a formula that can take a date field and tell me "Next Week", " In Two Weeks", "Next Month", "Future" (aka too far beyond next month)



------------------------------
Korrie Clark
------------------------------

7 Replies

  • If today happens to be say Friday March 29th, what result do you want in that field?   I think we need some examples of what you mean by Next Month? 



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

      regardless of actual calendar months i want next week to be the primary answer if it is in fact next week, but if its not in any of the other categories like 1-2 weeks next month basically just 4 weeks out would be next month, and anything beyond that would be too far into the future



      ------------------------------
      Korrie Clark
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        OK, not tested but try this.

        var date MyDate = [your date field here];

        var date SundayOfDateWeek = FirstDayOfWeek($MyDate);

        var Date SundayOfToday = FirstDayOfWeek (Today());

        IF(

        $MyDate < Today(), "Past",

        $SundayOfDateWeek = $SundayOfToday, "This Week",

        $SundayOfDateWeek = $SundayOfToday, + Weeks(1), "Next Week",

        $SundayOfDateWeek = $SundayOfToday, + Weeks(2), "Third Week Out",

        $SundayOfDateWeek = $SundayOfToday, + Weeks(3), "Fourth Week Out",

        $SundayOfDateWeek > $SundayOfToday, + Weeks(3), "4+ Weeks Out")

        If you want this to be sorted you can add some extra spaces in front of the words tpo dorce the sort or else number them like "0. Past" or "1. This Week"

         



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