Nested IF with MAX formula, comparing dates

[kschnaenberg]

Hello!

Pulled up a list of three dates from a linked table.  In this table, there is a separate date on each record.  

Looking for a way to find the latest of the three dates, that is not later than the separate date in the record.  I can find the last date using MAX, but am struggling to integrate the IF statement of not surpassing the stand alone date on the record.  

[Abdullah_Yuksel · Accepted solution]

I hope the screenshots I shared are explanatory 😊

[kschnaenberg · Accepted solution]

Thanks! 

Unfortunately that solution doesn't work for my use case.  This is part of an HR base.

1) Employee is listed on one table
2) On a separate table we list out effective dates for their salaries
3) Goals, of different types, are connected to an employee's salary.  On this table I am looking to pull up each employee's salary by date in order to establish yearly goals.  Salary's and hence goals change so I can't simply rely on latest.

This solution appears to work if I had one date reference back to the second table but there will be multiple.   

[kschnaenberg · Accepted solution]

I was able to revise my base design and use the solution provided, thanks!