How to automatically add NA to a date column in 1 case, which is updated manually in other cases?

neetuchopra05

Hi,

So, I have a date column, say Date A. I have another column say 'Condition A'. When 'Condition A' column consists of a value 'ABC', then Date A column should auto populate with 'NA'.

In other conditions when Condition A column has 'XYZ' or @'PQR', Date A column needs to be filled manually by the smartsheet user.

Now, what function/formula can I use to accomplish this task?

The Date A column is Date type, but not restricted to dates only. If I use workflow automation to 'change a cell value' when Condition A column consists 'ABC', it does not show date columns at all. So the task is not accomplished.

When I use an IF/Else formula in smartsheet column Date A, no one can add value manually. So, the purpose is defeated again.

Please advice how to accomplish both these things from Date A column in smartsheet.

Gary Collins

What if you change the date column to Text, then it should appear in your list of conditions in your automation

Andrée Starå

https://community.smartsheet.com/discussion/140053/how-to-automatically-add-na-to-a-date-column-in-1-case-which-is-updated-manually-in-other-cases

Hi,

I hope you're well and safe!

You could add so-called helper columns and use formulas to reference them, so you can get the text when needed and the date otherwise with the help of the Workflow(s).

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

neetuchopra05

@Gary Collins That wouldn't work as in other cases like 'PQR' etc., I need users to add Date to the cell value. If it will be a text column, they might get confused and stop filling it.

Anyway, thanks for responding.

neetuchopra05

@Andrée Starå How can helper column help me here? Please elaborate?

Did you mean, if Condition A column has ABC value, helper column goes to 1, else 0. And if helper column is values at 1, put NA for Date A column…?

But the question here remains, how to add NA to Date A column, when I need manual input for these cells in other conditions? <cries in a corner>

SueinSpain

Hi I don't have a complete answer for this but do have some suggestions.

I have a similar situation where I want variable results and the formula is too rigid. I have address 1 (mandatory) and address 2 (optional) and if address 2 is blank I want it filled in with address 1 (duh)

So what I have is 3 fields:

• Address 1 which is filled in
• Helper address - which is the optional field on a form
• Address 2 which is completed by the formula and then used going forwards.

My logic is if helper address is NOT blank then Address 2 = Helper address

If helper address IS blank then Address 2 = address 1

=IF([Helper Practice Address 1]@row = "", [Registered Address 1]@row , [Helper Practice Address 1]@row )

You could so the same with your dates

• Condition A is filled in
• Helper date is requested dependent on the value in Condition A (can be done in logic on a form)
• Date A has the formula - IF Helper is blank (or if Condition A ="ABC") then Date A = N/A (remember not to make a fixed date field). IF Helper is not blank (or Condition A <>"ABC" whichever works for you) then Date A = Helper date

Hope this makes sense & heps

neetuchopra05

@SueinSpain so, there are multiple aspects due to which helper column, as you described above, will not work.

Firstly, this smartsheet is already in use by users and they are responsible to fill Date A column. So, they will not agree to fill the helper column.

Secondly, I have 16 columns similar to Date A column and can not create 16 helper columns in my smartsheet. I don't think the management will allow that.

Thirdly, even if the management agrees to this, sending a form to request 16 dates for 16 columns for 600+ rows would be too hectic. And again users would object to it.

Sorry, but this solution doesn't help. Anyway, thanks for the reply.

SueinSpain

How do you get them to fill in the data in the first place?

If using a form then you can just have conditional logic that if Condition A is <> "ABC" the Date A pops up and is mandatory.

You can make your formula a column formula so it automatically acts for every row

You can use the update request to just send to users where the Condition A is <> "ABC" as it is no different for them to fill in the helper field as it is the Date field - they wouldn't know the difference.

Finally, if they want this type of thing automated then helper fields might be the only way to go.

My users fill in the form and although they are filling in the help feilds they think they are filling in the optional proactice address. Everything else is done behind the scenes and only the "real" Practice address is reported back to them once all the formula have calculated what it should be.

Sorry I couldn't help with a solution for you but good luck it will be very interesting to learn how to do this more efficiently and then I can improve my smartsheets as a result :)

Andrée Starå

https://community.smartsheet.com/discussion/comment/486641#Comment_486641

Ok.

Here are some other options.

1️⃣ . Could you use the Smartsheet API, Premium Apps, or a 3rd-party solution?

2️⃣ . What about using conditional formatting or an adjacent column to show a message?

✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

neetuchopra05

@SueinSpain Currently, they do not use a form to fill the data. They are manually entering the date in the smartsheet column, currently.

neetuchopra05

@Andrée Starå yes API can be used. Can you help me as in how to use it to resolve this purpose?

Also, Conditional formatting is done to grey it out. But the team also wants to add 'NA' to it.

As there are 16 such columns, adding adjacent columns would not be preferable here. Sorry.