Sharepoint Calculated field to next date

Glitter140 Unfortunately, you cannot use lookup fields in a calculated column. However, you can create it in the Lookup List where you can use calculated columns.

I did a small PoC which may give you some ideas.

Here is my Lookup List - Meetings:

I created a Meeting Date so it could easily be used in another calculated column. This column would check if that date is in the past:

Occurred values would then be used to assign a text value in the Next Meeting calculated column:

Then the Meetings list can be used in List/Document Library as a lookup list as field that accepts multiple values with The Meeting Date and Next Meeting to display in the view. In my case, I had created another list and created a lookup field called Meeting ID:

It's close to what you are looking for. Note that there is the semicolon separator but you can easily use advanced formatting to overcome that. It's not perfect since you still need to account for other dates that are in the future with this example, but that should give you some ideas.

You can use the following advanced formatting:

{

"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",

"elmType": "div",

"txtContent": "=join(split(@currentField, '; '), '\n')",

"style": {

"white-space": "pre-line"

}

}

 

This would display something like this (if applied to Meeting Date and Next Meeting):

 

Please like & mark as best response if this helped you 🙂