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

Best Answer

Answers

  • James Keuning
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!