Get Highest Value from Multiple Value Column

Question

Currently using SP Online Office 365

For simplicity let's say my list has 5 columns, The Lookup Date is linked to the lookup title so it pulls through the corresponding date based which title/s were pulled through the TitleLookup.

I am trying to look at TitleLookup:Date, separate the multiple values, find the largest value, and place that value in the Latest Date column.

Title - Date - TitleLookup - TitleLookup:Date - Latest Date

test - 1/1/10

Test2 - 2/2/12

Test 3 - 3/3/13 - Test;Test2 - 1/1/10;2/2/12 - 2/2/12

Preferably I would like to do this without any JQuery or the like, perhaps a workflow, that can log the lookup dates individually to its history and then compare them but while I'm ok at building basic workflows this is something a little more challenging for me.

Answer 1

Ok after a bit of work I managed to create a suitable workaround for my problem but any better solutions would be welcome.

Cons to this approach - 1.limited number of dates can be pulled through depending on how many spare columns you wish to create 2. only works if you know exactly how long each sub string will be (so is probably only suitable for something like a date)

Create a calculated column (calc1) as number =[date]+0 (force it to a number) create a single line of text column (text1) Create a workflow (2010 workflow) to set Text1 to calc1 (this is so we can use it in a lookup)

In the TitleLookup column, select the new text 1 column as an additional lookup

(this is so that when a string is created it is created as a number and not a strange date format which happened to me later on)

Create another single line of text column (text2)

In the workflow you created, make another set, in step two set to set the new single line of text column to the TitleLookup:Calculated column

create several calculated columns (however many you need) and run a =Mid function, =(SUM(MID([column],start at n number of characters 4,select the next n number of characters 16)+0) set as date and time

Your next column will start at letter 24 and count 16 letters.

Lastly create a column using the =MAX([calc1],[calc2],[calc3]) and set to date and time

set your workflow to update on creation or modification.

This will grab as many items as columns you created, and drag out the latest date.

Not the best solution but will work for now.