MAXIFS Formula to generate the latest date

Jessica Schuler

I'm working on building a Smartsheet workspace for our facilities team to track recurring maintenance tasks, and have had issues finding a formula that works the way I want. For context, I'm hoping the end result will be comprised of:

1) a maintenance log

2) a maintenance overview sheet, which shows the most recent "Date Completed" from the Maintenance Log by "Building Name" & "Task Completed" (similar to a pivot table)

So far, I haven't found a formula to make the overview sheet function the way that I want. Any recommendations?

A few additional notes:

-I'd like to utilize column formulas to ensure ease of maintenance if at all possible (e.g. @ row to index the building name, etc.)

-After identifying a formula, I plan to build out the overview sheet to show when a building has a maintenance task that's behind schedule.

Screenshots:

1) Maintenance Log:

2) Overview Sheet (example: based on the maintenance log, the highlighted cell for "Animal Control Shelter" and "Fire Alarm Inspection" should show 02/29/24 since it's the most recent date from the list)

AravindGP

Hi @Jessica Schuler

You can try this formula for your Fire Alarm Inspection. You will need to replace the search value for each activity done in your Overview sheet.

=IF(CONTAINS("Annual Fire Alarm Inspection", {Task Completed Reference in Maintenance Log}), MAX(COLLECT({Date Completed reference in Maintenance Log}, {Building Name reference in Maintenance Log}, [Building Name]@row, {Task Completed Reference in Maintenance Log}, "Annual Fire Alarm Inspection")))

The formula looks if the task exists in the maintenance log, and if it does find it, it will pick up the latest date of completion that corresponds to the same task and building.

AravindGP

Hi @Jessica Schuler

You can try this formula for your Fire Alarm Inspection. You will need to replace the search value for each activity done in your Overview sheet.

=IF(CONTAINS("Annual Fire Alarm Inspection", {Task Completed Reference in Maintenance Log}), MAX(COLLECT({Date Completed reference in Maintenance Log}, {Building Name reference in Maintenance Log}, [Building Name]@row, {Task Completed Reference in Maintenance Log}, "Annual Fire Alarm Inspection")))

The formula looks if the task exists in the maintenance log, and if it does find it, it will pick up the latest date of completion that corresponds to the same task and building.