Closest Past Date from a list of dates

Question

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

markshnier__you · Answer

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

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

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

karthiksekhar1 · Answer

Yes------------------------------Karthik Sekhar------------------------------

markshnier__you · Answer

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

Forum Discussion

Closest Past Date from a list of dates