Formula to Calculate Date from Linked Table

[gospelsoulcare]

I have a base that has client names in a table and appointment date on another table that is linked to the specific client. I am trying to create a formula in the client table that will display the last appointment for that that client that was scheduled and another formula field that will display the next appointment date for that client. 

When I try to create a formula to look at the linked appointment date table from clients, it says it can't be calculated because it's not a date. 

Is there any way to make this work, which I am missing? Thanks in advance.

[Pascal_Gallais- · Accepted solution]

Hello,

One way to do that is to transform appointments dates in number so that you can use MIN and MAX functions.

Here is a table of appointments with 4 dates for the same client:

I add 2 fields to this table:

"Number_Date" transforms the date into a number:

"Status" is a formula to specify if it is a past date or a date to come:

I can now add 2 rollup fields in table client to get the last and next appointments:

If needed, some more formulas can be added to table Client transform "Last" and "Next" as date fields.

Regards,

Pascal

[gospelsoulcare]

This worked great for me. Thanks!