MAX(COLLECT) Formula Returning Invalid Operations Error when Number Begins with 0

Lori Flanigan

Hello,

I have a column formula that has been working in a sheet to designate the latest entry for an employee based on their employee ID.

=IF(MAX(COLLECT([Ship Date]:[Ship Date], [Employee ID]:[Employee ID], [Employee ID]@row)) = [Ship Date]@row, 1, 0)

Today, some employee IDs that begin with 0 were entered. Those rows now have an INVALID OPERATION error.

I used the AI formula builder to check my formula, but nothing differed.

How can the formula be change to handle leading 0's?

Thanks!

Lori

KPH

Hi @Lori Flanigan

If the cell has a leading 0 it is not recognized/handled by Smartsheet as a number. It will be text. You can't do math on text. If you click into the cell you will probably see a little apostrophe at the start of the "number".

You can convert text to number using the VALUE function, i.e. VALUE([Employee ID]@row) . Then use this in your MAX function. It will however, strip all the leading 0s. Here's hoping that's OK. 😏

Lori Flanigan

Thanks. Another instance where leading zeros are a problem.

I'll submit another enhancement request for numbers with leading zeros to be recognized as numbers.

Lori