Forum Discussion

Qrew Trainee

2 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 ...

MarkShnier__You

Qrew Champion

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
------------------------------

Qrew Trainee

2 months ago

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
Qrew Champion
2 months ago
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
------------------------------
- KorrieClark
  Qrew Trainee
  2 months ago
  I am getting an error on the 1 week line
  
  var date MyDate = [Estimated Review Start Date];
  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), "2 Weeks Out",
  $SundayOfDateWeek = $SundayOfToday, + Weeks(3), "3 Weeks Out",
  $SundayOfDateWeek > $SundayOfToday, + Weeks(4), "Next Month")
  
  ------------------------------
  Korrie Clark
  ------------------------------
  - MarkShnier__You
    Qrew Champion
    2 months ago
    I am just boarding a plane now, and I don't know if it will have Wi-Fi. The problem is that I incorrectly included a comma after all of the occurrences of
    
    $SundayOfToday
    
    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------