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

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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.