Find largest date for a given task from a list of multiple tasks

PaulPerger

I have a need to lookup the most recent date upon which a task was completed. Each task has it's own unique number and upon completion an entry is made into an "archive sheet" of tasks completed in the past.

So, a very simplified example is that I am looking to utilize a formula to find the largest (i.e. most recent completion) of a task from a list of tasks that will have multiple occurences of multiple tasks.

HISTORY SHEET

Task # - Date Completed

001 - 9/15/22

002 - 9/15/22

003 - 9/17/22

001 - 9/18/22

002 - 9/19/22

001 - 9/20/22

I need to use a formula to look up the largest date for each task.

On my master Task List, this would be the result I am looking for.

MASTER TASK LIST SHEET

Task # - Last Completed

001 - 9/20/22

002 - 9/19/22

003 - 9/17/22

Darren Mullen

@PaulPerger MAX([DATE RANGE]) will give you the most recent date, will that work?

https://help.smartsheet.com/function/max

PaulPerger

https://community.smartsheet.com/discussion/comment/344939#Comment_344939

That will return the largest date in the column, but I would need the largest date for each corresponding task #. I.E in my example, the MAX function would always return "9/20/22", but I need to see the largest date for EACH of the task IDs, i.e. for Task 001 - 9/20/22 but for Task 002 - I need to see 9/19/22 (i.e. the largest date on which Task #002 was completed).

Darren Mullen

@PaulPerger Use COLLECT() to create a range that collects all the dates where a Task# = a specific task number. You can then use MAX on that range.

Where are you trying to display the date that you find?

PaulPerger

https://community.smartsheet.com/discussion/comment/344955#Comment_344955

I am trying that, but apparently I am not understanding the required syntax.

I am writing a formula in a master sheet to collect the last date completed so I can use that date to calculate the next due date for said task.

Darren Mullen

@PaulPerger What syntax are you using? You can collect dates from your archive sheet that match your task #.

PaulPerger

https://community.smartsheet.com/discussion/comment/345066#Comment_345066

Darren... I figured it out after a response to another post. Turns out my syntax was exactly what it should have been. My lookup value was changing from one worksheet to the next...

Once I figured that out, everything started working perfectly.

Thanks for your help!