Presenting N/A or Entering Date in Reports

Hello,

We are attempting to come up with a formula that will present N/A in a Date column if the parent column lists N/A, otherwise we want the date functionality to work by presenting a calendar the user can select the date from on their individual report.

What we are running into when the report is created is that the formula is overtaking the "Date" function and not allowing the user to enter anything in their Date column.

The formula that the owner of the report created was:

=IF([7. Asset Mgmt-FM/PM Elect]@row = "N/A", "N/A", " ")

Could you please help us come up with a formula that will allow both to present in an individual report?

Thank You,

Dustin

Find more posts tagged with

Formulas

Reports

Accepted answers

Paul Newcome

If a cell contains a formula, it cannot be edited through a report. Additionally, if a cell containing a formula is manually changed, it will delete the formula from the cell entirely.

To make this work in a report, you will need two separate date type columns. One containing a formula and another that is strictly manual entry.

All comments

James Keuning

If you want Field A to contain a formula, you cannot manually enter data into Field A.

I think you want a formula which points to a third field for the data.

Try this:

Create three dates fields:

Parent, OurField, and Override

In OurField, put this formula:

=IF(ISBLANK(Parent@row), IF(ISBLANK(Override@row), "Enter date in Override field", Override@row), Parent@row)

Also, it's a little confusing when you refer to a parent column, because in Smartsheet, parents are usually rows, viz a row with indented children. Because of that, maybe I am not understanding the question.

Dustin Whitehead

Hi James,

Not a parent column per se, what I mean is that we have a column that will auto-populate a response based on data entered in another column, however that target column is also a Date field and I was referring to the column it was pulling from as the "parent" column.

What my co-worker is wanting is to keep the date field available but also have the cell fill with "N/A" if the column it is pulling from is also N/A.

She is creating a sheet that tracks the dates certain employees complete different training programs but needs the date field to present as N/A if that particular training program is not required for that employee and the column is listed as N/A.

Hope that helps.

Thank You

Paul Newcome

If a cell contains a formula, it cannot be edited through a report. Additionally, if a cell containing a formula is manually changed, it will delete the formula from the cell entirely.

To make this work in a report, you will need two separate date type columns. One containing a formula and another that is strictly manual entry.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.