Formula to determine next available date

TPALJA

Im trying to determine the next available date based on end date, Job status as active and the event type contains install. This formula returns a date =MAX(COLLECT({End Date}, {Job Status}, "Active"))

Ive tried adding the contains install but it doesnt return a date

=MAX(COLLECT({End Date}, {Job Status}, "Active", {Event Type}, CONTAINS("Install))

JBG

Hi @TPALJA

You would need to add a helper column"ContainsInstall" to determine if the event column per row contains the text "Install".

In this column you would then have the following formula:

=IF(FIND("Install"; [Event Type]@row) > 0; 1; 0), which would populate the cell with a 1 if the text "install" is in the event column.

You can then replace your formula with the following:

=MAX(COLLECT([End Date]:[End Date]; [Job Status]:[Job Status]; "Active"; ContainsInstall:ContainsInstall; 1))

Which should return the correct answer.

NOTE: I usesemicolon due to comupter settings. You might have to use comma.

=============================================

"Nothing is impossible. The word itself says 'I'm possible!'"