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

Lori Flanigan
Lori Flanigan ✭✭✭✭✭
edited 03/14/24 in Formulas and Functions


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?




  • KPH
    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
    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!