Find next date in a list and give the column header

[masonrtomko]

I am honestly not sure how to phrase my question without an example. 

Item	Month 1 Timepoint	Month 2 Timepoint	Month 3 Timepoint	Month 4 Timepoint	Month 5 Timepoint	Month 6 Timepoint	Next Timepoint
Apple	04/14/2023	05/14/2023	06/14/2023	07/14/2023	08/14/2023	09/14/2023	Month 1 (04/14/2023)
Banana	11/13/2022	12/13/2022	01/13/2023	02/13/2023	03/13/2023	04/13/2023	Month 5 (03/13/2023)

I want to make a virtual column (in this example, "Next timepoint" is the virtual column) that gives me the column header and date of the next timepoint. For example, if I wanted to retrieve the timepoint that is upcoming.

I am thinking that I could make a list of the dates for each item, remove past dates, and calculate the month duration of the first date that wasn't removed, which is assumed to be the next testing date. However, I haven't been able to get the proper syntax started.

[Marc_Dillon]

There is no Appsheet formula that can return the name of a column. You can achieve this, but you have to basically hard-code it like this:

IFS(
  [col1] = MAX( LIST( [col1],[col2]...)) , 
  "col 1" ,
  [col2] = ...
  ...
)

Your data structure is not very good, for this reason and more. A better structure would be like this:

Item	Month	Timepoint
apple	1	4/14
apple	2	5/14
apple	3	6/14

[dbaum · Accepted solution]

It's a lot easier to sort and select rows as compared to columns. You're likely better off segregating your Month Timepoint columns into a child table with each row referencing a parent item and having a single Month column. For example

Parent table

Item

Apple

Banana

Child table

Item	Month	Timepoint

Using your current data structure, there's no way to avoid needing a dense expression.

You could create a brute force series of comparisons:

IFS(
    TODAY() < [Month 1 Timepoint], CONCATENATE("Month 1 (", [Month 1 Timepoint], ")"),
    TODAY() < [Month 2 Timepoint], CONCATENATE("Month 2 (", [Month 2 Timepoint], ")"),
    ...
    )

Alternatively, you could try a more abstract INDEXOF technique:

CONCATENATE(
    "Month ",
    COUNT(
        SPLIT(
            ANY(
                SPLIT(
                    (" , " & SORT(LIST([Month 1 Timepoint], [Month 2 Timepoint], [Month 3 Timepoint], [Month 4 Timepoint], [Month 5 Timepoint], [Month 6 Timepoint], DATE(TODAY()))) & " , "),
                    (" , " & TEXT(TODAY(), "MM/DD/YYYY") & " , ")
                )
            ),
            " , "
        )
    ),
    " (",
    INDEX(
        LIST([Month 1 Timepoint], [Month 2 Timepoint], [Month 3 Timepoint], [Month 4 Timepoint], [Month 5 Timepoint], [Month 6 Timepoint]),
        COUNT(
            SPLIT(
                ANY(
                    SPLIT(
                        (" , " & SORT(LIST([Month 1 Timepoint], [Month 2 Timepoint], [Month 3 Timepoint], [Month 4 Timepoint], [Month 5 Timepoint], [Month 6 Timepoint], DATE(TODAY()))) & " , "),
                        (" , " & TEXT(TODAY(), "MM/DD/YYYY") & " , ")
                    )
                ),
                " , "
            )
        )
    ),
    ")"
)