calculate maximum date


Hi everyone,

I need your help to calculate the maximun data for each resource, starting from a sheet where the activities are recorded.

I have a sheet where I write all tasks for each resource:

I need to have the maximum date present in the "task" sheet for each resource present in the "resources" sheet

Can you please help me ?

I tried using the MAX function with COLLATE but I didn't get the result.
I also tried to create a report, but I can't find the result because in the TASK sheet the main column is TASK and not the RESOURCE.

I need this to monitor resource management tasks: understand which resources I no longer have planning for the next days and receive an alert from smartsheet.

Thanks a lot to everyone


  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 06/11/24

    Hey @malanto,

    Do you need to find the max date for the Start Date, End Date, or both? Currently here's how you would do it if you're just looking to find the max End date for resource "Test":

    =MAX(COLLECT([End Date]:[End Date], Resource:Resource, "Test"))

    You would want to make sure the column this formula is entered into a Date column. Unfortunately I'm not sure if MAX/COLLECT allows for the range being collected to be more than 1 column wide as I tried doing it across both start and end date columns but it was returning invalid.

    What you could do is have "Max Start Date" and "Max End Date" columns, then have a third column "Max Date", that just returns the max of the Max Start Date and Max End Date columns?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!