Formula working in some cells in Smartsheet but not others.

I am using this formula to determine if a particular training asset is on schedule or behind schedule by comparing the draft start and end dates. If the time between is greater than 5 days then Smartsheet returns red and if the time between is less than 5 days, Smartsheet returns green.

=IF(NETWORKDAY([First Draft Actual Start Date]@row, [Draft Actual End Date]@row) > "5", "Red", "Green")

The formula works for most cells but some cells return the incorrect color (for example if the training asset start and end date is past 5 days, green appears instead of red). Why is this happening? Has anyone experienced something similar?

Find more posts tagged with

Accepted answers

KPH

Can you remove the quotation marks around 5:

=IF(NETWORKDAY([First Draft Actual Start Date]@row, [Draft Actual End Date]@row) > 5, "Red", "Green")

All comments

KPH

Can you share an example of a row where it does not work, and what you wish it would show?

RCS

@KPH The green should truly be red since there are more than 5 days between the actual start and end dates. It might be because of the year difference because the items that are working are all in the same year..

RCS

@KPH I wish the green icon would show as red since there are greater than 5 days in between. The error is happening when the draft actual end date goes into a new year. That might be part of it?

KPH

Can you remove the quotation marks around 5:

=IF(NETWORKDAY([First Draft Actual Start Date]@row, [Draft Actual End Date]@row) > 5, "Red", "Green")

RCS

@KPH Yes!!! The small details matter! Thank you so much for your help!

KPH

Great! Glad I could help. Sometimes all you need is fresh eyes.

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.