Excel to Smartsheet Equation Transfer

Hi, I just have a question relating to transferring an equation from Excel to Smartsheet. The below is what I have on Smartsheet, having amended the row references from the Excel format to Smartsheet's, but I'm having an issue where whenever a date is inserted in the [Date Due]@row, the formula just returns the "Contact Admin" placeholder. Otherwise the cell just appears blank.

Would it be possible to help figure out how best to transfer it to Smartsheet (in Excel, if the [Investigation Completed Date]@row is blank, then it just returns the calculated number of days to go as a numerical value (using 30 as the number of days until overdue, or as a minus value if more than 30).
Is it related to the error I get on Smartsheets, where if I just do =[Date Due]@row + 30 to get the date + 30 days then I get a #INVALID COLUMN VALUE error? And if so, how to address that if I just want to add 30 days onto a date?

=IFERROR(IF([Date Due]@row = 0, "", (IF([Investigation Completed Date]@row = 0, ([Date Due]@row - 30), IF([Investigation Completed Date]@row > [Date Due]@row, "Complete - Overdue", IF([Investigation Completed Date]@row < [Date Due]@row, "Complete - On Time", "Complete - On Time"))))), "Contact Admin")

Any help is greatly appreciated!

Find more posts tagged with

Accepted answers

All comments

SoS | Dan Palenchar

Hello @Alex Br,

First of all, I would check you have the appropriate column types. If your formula is going to output a date then you need to be using a date column. This would apply to your example of =[Date Due]@row + 30 which will give you the date 30 days after whatever is in the Date Due column but only if you input it into a Date column.

For your larger formula there is obviously an error somewhere seeing as the IFERROR() is kicking in with "Contact Admin". This again could be due to not using a Date column. I noticed you also have several IF() statements where you are evaluating for [Date Column]@row = 0. Are you checking for a blank cell? If so, rather than doing = 0 try either:

IF([Date Column]@row = "", [Value if true]…
IF(ISBLANK([Date Column]@row), [Value if true]…
IF(NOT(ISDATE([Date Column]@row)), [Value if true]…

I don't think [Date Column]@row = 0 would throw an error necessarily but it is not how I would structure this type of formula unless you are literally checking for a 0 value.

If you can't get it to work remove the IFERROR() portion and see what type of specific error you are getting which can help determine how to fix this. The formula for this would be:

=IF([Date Due]@row = 0, "", (IF([Investigation Completed Date]@row = 0, ([Date Due]@row - 30), IF([Investigation Completed Date]@row > [Date Due]@row, "Complete - Overdue", IF([Investigation Completed Date]@row < [Date Due]@row, "Complete - On Time", "Complete - On Time")))))

Alex Br

Thanks @Dan Palenchar that's sorted the issue! So part of my problem seemed to be the = 0 issue, where in Excel, I used it to check if the cell was empty. However this didn't quite transfer to Smartsheets the same way. Using "" seems to have solved the issue!

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.