Using MAX to get latest date by criteria

Options

Hello,

I am trying get the latest date in a list, subject to criteria but MAX COLLECT does not seem to work. What am I doing wrong? I have tried both a text column and a date column.

=MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),)

Appreciate any suggestions!

Best Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 07/21/23 Answer ✓
    Options

    HI @Jbob

    Thanks for the post. I have tried to recreate your example (in a simple way) to see where the issue is.

    In mine the column called Date and the column called Max per Value are BOTH Date data types.

    I have exposed the formula I have used in the last column to show how the Max per Value is coming through.

    I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?

    Does this help at all?

    So in yours:

    =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),)

    Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.

    Kind regards

    Debbie

  • Jbob
    Jbob ✭✭✭
    Answer ✓
    Options

    Hello Debbie,

    Appreciate your help, that worked perfectly!

    Thank you!

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 07/21/23 Answer ✓
    Options

    HI @Jbob

    Thanks for the post. I have tried to recreate your example (in a simple way) to see where the issue is.

    In mine the column called Date and the column called Max per Value are BOTH Date data types.

    I have exposed the formula I have used in the last column to show how the Max per Value is coming through.

    I know this has simplified your original question, but can you see how the Max(Collect()) statement works here?

    Does this help at all?

    So in yours:

    =MAX(COLLECT({CQ Hours Data Range 6}, {CQ Hours Data Range 2}, Model@row, {CQ Hours Data Range 5}, Batch@row),)

    Range 6 and the column you have written this formula into must be Date fields and you have an extra comma where you don't need it! Just between the last two )). This comma will cause an error.

    Kind regards

    Debbie

  • Jbob
    Jbob ✭✭✭
    Answer ✓
    Options

    Hello Debbie,

    Appreciate your help, that worked perfectly!

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!