SUM, MAX of many date columns per row to find latest timestamp

[Steve_Chambers]

Hi guys.

I have an “Orders” table that has 9 columns that track a changetimestamp on the [Progress] and I am wanting to give the user the ability to: order by the most recently updated progress. So in a VC i am looking at:
MAX([Creation Date Stamp],[Quote Sent Date],[Deposit Paid Date],[…])
and then wrap this in a SUM(MAX()TODAY+1) to give a final result of:

[Order Number] [Last Progress Update]
[1] |4 days ago|
|2| |5 days ago|
|3| |8 days ago|
|4| |17 days ago|
|5| |28 days ago|

If that makes sense? I am struggling with the MAXROW expression

[Suvrutt_Gurjar]

If I have understood your requirement correctly, could you try in the VC of [Last Progress Update]

CONCATENATE( (TODAY()- MAX(DATE([Creation Date Stamp]) , [Quote Sent Date],[Deposit Paid Date],[…])), " days ago")

[Steve_Chambers]

I can’t get past the ‘MAX function used incorrectly’

I think you have understood me correctly, to clarify i have 9 columns in my Orders table that house datetime values, i would like to build a VC that finds that latest (MAX) datetime for each row

then wrap it in concat((today - max(date()))
Its just the max date i’m struggling with

[Suvrutt_Gurjar]

Please try

CONCATENATE( (TODAY()- MAX(
LIST(DATE([Creation Date Stamp]) , DATE([Quote Sent Date]),DATE([Deposit Paid Date]), DATE([…]))), " days ago")

[Steve_Chambers]

Thanks
your formula is working!
However it’s displaying as a duration, its outputting the result in hhh:mm:ss ie 72:00:00

I think i need to convert a DateTime value to a Time value with TIME() , then subtract a zero time to convert a Time value to a Duration value: HOUR(TIME(NOW()) - "00:00:00") as per HOUR() expression

I had a second VC working for a brief second until i realized the original VC with the below formula was changed to decimal, which broke the app

I need to divide the duration by 24 somehow i think, here’s the working formula to output hours since MAX

(TODAY()- MAX(
LIST(
DATE([Creation Date Stamp]) ,
DATE([Quote Sent Date]) ,
DATE([Deposit Paid Date]) ,
DATE([Confirmation Sent Date]) ,
DATE([Production Start Date]) ,
DATE([Production End Date]) ,
DATE([Invoice Sent Date]) ,
DATE([Invoice Paid Date]) ,
DATE([Follow-up Letter Sent Date])
)))

to get that to days since max, something like this maybe?

HOUR(NOW() - (DATETIME(TODAY()) - [Last Updated]) )/24 ??

[Suvrutt_Gurjar · Accepted solution]

Thank you @Steve_Chambers . Good to know it basically works.

You are correct. please wrap the result by HOUR()

Maybe something like

HOUR((TODAY()- MAX(
LIST(
DATE([Creation Date Stamp]) ,
DATE([Quote Sent Date]) ,
DATE([Deposit Paid Date]) ,
DATE([Confirmation Sent Date]) ,
DATE([Production Start Date]) ,
DATE([Production End Date]) ,
DATE([Invoice Sent Date]) ,
DATE([Invoice Paid Date]) ,
DATE([Follow-up Letter Sent Date])
)))) / 24

Please refer the first duration expression example in the article below under the section " Examples that Compute Durations in Days, Months, or Years"

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

[Steve_Chambers]

oh no you’ve gone and done it now, i’ll never leave you alone

thanks for the link, i’ll dive in soon the scheduling system is the next view i will build