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 20...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendAre 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
------------------------------
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
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
------------------------------
------------------------------
Karthik Sekhar
------------------------------
- MarkShnier__YouCan 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
------------------------------- Yes
------------------------------
Karthik Sekhar
------------------------------- MarkShnier__YouOK, easy peasy,
Look up Reference date down to the child table.
Then make a summary field on the relationship to find the Maximum Past Date subject to the filter that the Past Date is less than the Reference Date.
(not sure if you want less than or less than or equal to)
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------