Forum Discussion

KarthikSekhar1's avatar
KarthikSekhar1
Qrew Member
2 years ago

Closest Past Date from a list of dates

I need to come up with a formula that evaluates the closest past date from a reference date. For example, If the dates are Jan 2022, May 2022 and my reference is April 2022, then I must go with Jan 2022 and not May 2022.

------------------------------
Karthik Sekhar
------------------------------
Formulas and functions
Relationships

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago
    Are these various past dates in a child table? Is the reference date on the parent record and the child Table has all of the possible past dates to evaluate?

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • KarthikSekhar1's avatar
      KarthikSekhar1
      Qrew Member
      2 years ago
      In my case I am trying to write a formula for each month of this year. For example, I will have a formula field called Apr 2022. So, Firstdayofmonth(Apr2022) is my reference. The other dates are coming from child table. I am using the Max function to get the Max date and then Max Date - 1. Lets say the Max Date is May 2022 and Max Date - 1 is Jan 2022. need to be able to write a formula for Apr 2022 with these as my inputs. The formula should evaluate to Jan 2022 (i.E. the nearest past date).

      ------------------------------
      Karthik Sekhar
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew #1 Challenger
        2 years ago
        Can you confirm that all these fields are date field types? I.e. not just the words in a text field but actual date fields?

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