help with formula

Monica Solar

Hi Everyone - I'm new to this community and wondering if I can get some help with writing a formula to do the following:

If Tag column contains the words “Day 1” then Actual Start Date column should equal the value of Employment Enablement Date plus the value of Business Day column

If Tag contains “Day 1” and Legal Close column is checked then Actual Start Date should equal  Legal Close Date plus Business Day

If Tag does not contain “Day 1” then leave Actual Start Date blank

I've come up with these formulas but cant' figure out how to put them together...

=IF(CONTAINS("Day 1", Tag@row), WORKDAY([Employee Enablement Date]@row, [Business Day]@row), "")

=IF(AND(Tag@row = "Day 1", [Legal Close]@row = 1), WORKDAY([Legal Close Date]@row, [Business Day]@row), "")

=IF(Tag@row =””, “”)

help?

Thanks,

Monica

Kelly Moore

Hey @Monica Solar

A nested IF is a series of IF statements. The order of the IFs are important as the equation stops reading Left to Right as soon as the first terminating true statement is found.

=IF(Tag@row<>"", IF(AND(Tag@row = "Day 1", [Legal Close]@row = 1), WORKDAY([Legal Close Date]@row, [Business Day]@row), IF(Tag@row = "Day 1",WORKDAY([Employee Enablement Date]@row, [Business Day]@row))))

Will this work for you?

Kelly

Monica Solar

Hi Kelly! Wow! Just wow! Thank you so much! Yes, this works! Really appreciate you so much! Thank you for taking the time to help me with this. All the best - Monica

Kelly Moore

Hey @Monica Solar

For the ability to have a formula and have manual entries you need another column, in your case a date column. Think of it as a manual override column. You can call it what you like.

=IF(Tag@row<>"", IF(AND(Tag@row = "Day 1", [Legal Close]@row = 1), WORKDAY([Legal Close Date]@row, [Business Day]@row), IF(Tag@row = "Day 1",WORKDAY([Employee Enablement Date]@row, [Business Day]@row)))),[manual override]@row)

try this and see if it will work for you.

Kelly

Kelly Moore

Hey

So glad it's working now

Kelly

Kelly Moore

Hey @Monica Solar

A nested IF is a series of IF statements. The order of the IFs are important as the equation stops reading Left to Right as soon as the first terminating true statement is found.

=IF(Tag@row<>"", IF(AND(Tag@row = "Day 1", [Legal Close]@row = 1), WORKDAY([Legal Close Date]@row, [Business Day]@row), IF(Tag@row = "Day 1",WORKDAY([Employee Enablement Date]@row, [Business Day]@row))))

Will this work for you?

Kelly

Monica Solar

Hi Kelly! Wow! Just wow! Thank you so much! Yes, this works! Really appreciate you so much! Thank you for taking the time to help me with this. All the best - Monica

Monica Solar

Hi @Kelly Moore just tested my formula further and realized the first part of it isn't quite working as expected, what I'm trying to do is:

If Tag does not contain “Day 1” then leave Actual Start Date blank, however, what I neglected to mention is Actual Start Date is a Date column, so I want the DATE function to be available vs. actual blank - my goof! sorry. Any thoughts on how to fix ?

Kelly Moore

Hey @Monica Solar

I think you are saying that you want to have both a formula and manual entry available in the same column. If this is correct, then no, you can't - and consistently rely that the formula will be there. Once overwritten it will not self populate.

There is a workaround with an additional helper column. I'll wait to explain until after you confirm my interpretation of your request was correct.

Kelly

Monica Solar

Hi @Kelly Moore Yes, correct! That's exactly what I want. I want the formula for the other two scenarios, but if Tag does not contain Day 1, I want ability to manually enter a date (or pick the date from the DATE function)...

Kelly Moore

Hey @Monica Solar

For the ability to have a formula and have manual entries you need another column, in your case a date column. Think of it as a manual override column. You can call it what you like.

=IF(Tag@row<>"", IF(AND(Tag@row = "Day 1", [Legal Close]@row = 1), WORKDAY([Legal Close Date]@row, [Business Day]@row), IF(Tag@row = "Day 1",WORKDAY([Employee Enablement Date]@row, [Business Day]@row)))),[manual override]@row)

try this and see if it will work for you.

Kelly

Monica Solar

Thanks @Kelly Moore . I'll test it out and let you know!

Monica Solar

Hi @Kelly Moore Yes! That seems to do the trick! Again, thank you so much!

Monica Solar

hi @Kelly Moore I have one more question for you. The formula is working as expected but I just thought of one more use case and wondering if you think it's possible. Let's day Tag does not have Day 1, and someone manually enters a date - that works great. But then later they decide it is a Day 1 activity, when you set Tag to Day1, it keeps the Date that was enter in manually. Is there a way to reset it back to DATE function?

thanks!

Monica

Kelly Moore

Hey

I think I'm confused, sorry. Did you add an additional column for the manual entry or are you still trying to enter formulas and dates in the same column? If you're asking if the formula column will ever be a regular date column you can freely enter dates into, then no, it cannot be both a formula column and a regular empty date column.

If you added the helper column as described above, the one I loosely called manual override, and the formula exists in your desired Actual Start Date column then, if someone eventually does enter a Day 1 in Tag, the formula will revert to the date dictated by the formula: IF(AND(Tag@row = "Day 1", [Legal Close]@row = 1), WORKDAY([Legal Close Date]@row, [Business Day]@row), IF(Tag@row = "Day 1",WORKDAY([Employee Enablement Date]@row, [Business Day]@row)))

Did I answer your question? Please ask me again if I did not

Kelly

Monica Solar

Hi @Kelly Moore - yes, I added the date helper. here is my formula:

=IF(Tags@row <> "", IF(AND(Tags@row = "Day 1", [Legal Close Anchor]@row = 1), WORKDAY([Legal Close Date]@row, [C- / C+ (Business Days)]@row), IF(Tags@row = "Day 1", WORKDAY([Employee Enablement Date]@row, [C- / C+ (Business Days)]@row))), [Date Helper]@row)

What I'm struggling with is if Actual Start Date is a text column then I can enter my formula, but I get "invalid column value" error. If I change the Actual Start Date column to date column, the formula works initially, but then gets overwritten when someone manually enters a date...

thanks,

Monica

Kelly Moore

Hey Monica

If you convert this formula to a column formula, others will not be able to enter any dates into the column. A column formula locks entries out. Are you familiar with using column formula function? If not, right click within a cell with the correct formula. Scroll down to the bottom of the cell menu to turn column formulas on. NOTE: You must also open the cell menu to do any editing to the formula once it becomes a column formula.

This would force any additional entries into the manual date field. According to your formula, soon as your Tag field gets populated this formula takes over. I have assumed this formula resides in the [Actual Start Date] column, correct? If yes, I'm confused why anyone would be entering a date here since we have discussed having a formula and manual entry within the same column, which is why the manual helper was added.

I'm sorry I am unclear on your workflow. Please keep asking questions until we have this resolved.

Kelly

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Monica Solar

Hi @Kelly Moore - Thank You! Yes, I am familiar with column formulas, but I am new to creating formulas in Smartsheet....so still learning. Ok, I think the formula is working now. Here is how I set it up and it seems to be working.

1. Actual Start Date column is column type Date
2. Formula is in the Actual Start Date column
3. Actual Start Date is column formula
4. Date Helper is Date column (and where User will enter "manual override" date

Now I'm going to add in some error checking for the use case when expected data is missing, for example, Day 1 or Legal box are checked but Legal Date, Employee enablement or C1/C+ dates are missing...but the formula is working.

thank you!

Monica

Kelly Moore

Hey

So glad it's working now

Kelly

Monica Solar

Thank you @Kelly Moore