Forum Discussion

Glitter140's avatar
Glitter140
Copper Contributor
Nov 03, 2023

Sharepoint Calculated field to next date

Hi ,

 

I am trying to add a calculated field to a list that takes another field (which allows multi values) and picks up the next date.

 

See Below - Meeting ID is a lookup from another list and is populated via Power Automate.  I would like to populate the Next Meeting column with the next date (equal to or after today) so row 1 it would be Feb 24, for row 2 it would be Nov 23.

Any ideas of a formula that would handle this?

 

Thanks

 

SharePoint Online
  • Tristan999's avatar
    Tristan999
    Iron Contributor
    Nov 03, 2023

    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:

     

    {
    "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 🙂

     

     

Resources