Using IF function to return 3 different values to cell based on date

Shelton Alves

Hello!

I am currently using a Smartsheet for contract management. For the purposes of my question, it is three columns that are important. The Contract status, the contract start date, and contract cancel date, if and when applicable.

The three statuses I use are Active, Inactive, and Onboarding. Active needs to return "Active" when their start date is <= TODAY. Status needs to return "Onboarding" if start date >=TODAY. Both of these return values look at the Contract Start date column.

Then "Inactive" refers to the Cancel date column. If the Cancel date is blank, I would like it to return the current value it is in between the "Active" or 'Onboarding. Once an inactive date is present within the cell, I would like it to keep the current status in place until the cancel date is <=TODAY so it switches to say "Inactive".

I would think it would be odd to have a cancel date come before a contract is in 'Onboarding' because I would probably delete that row all together (that would be very rare anyhow).

I got this far : =IF([Cancellation Effective]@row <= TODAY(), "Inactive", IF([Start Date for Phase 1]@row <= TODAY(), "Active", IF([Start Date for Phase 1]@row >= TODAY(), "Onboarding")))

However when I clicked and began to drag it down, it would switch statuses that previously said "Active" (because I have been doing it manually from a drop down) it switched those to "Inactive" because I noticed the Cancel date cells for those was blank. I tried to nest an If BLANK function within it, but there is where I was stuck.

Thank you for any help!!!

Paul Newcome

Blank date cells are considered less than TODAY(), so you would want to use an AND statement to include something about the [Cancellation Effective] column not being blank in the first IF.

=IF(AND([Cancellation Effective]@row <> "", [Cancellation Effective]@row <= TODAY()), "Inactive", IF([Start Date for Phase 1]@row <= TODAY(), "Active", IF([Start Date for Phase 1]@row >= TODAY(), "Onboarding")))

Paul Newcome

Blank date cells are considered less than TODAY(), so you would want to use an AND statement to include something about the [Cancellation Effective] column not being blank in the first IF.

=IF(AND([Cancellation Effective]@row <> "", [Cancellation Effective]@row <= TODAY()), "Inactive", IF([Start Date for Phase 1]@row <= TODAY(), "Active", IF([Start Date for Phase 1]@row >= TODAY(), "Onboarding")))

Shelton Alves

Awesome-thank you very much Paul! This has been a fun project to learn in with SmartSheets and all you can do. This was a huge help for my process.

Have a great day!!

Paul Newcome

Happy to help! 👍️

Shelton Alves

Hey Paul!

I actually had an additional question within the same form that I came across that deals with the Active, Inactive, and Onboarding status.

For all the contracts I manage, it is for different properties. In my sheet, I totaled up the number of properties for all statuses (Active+Onboarding+Inactive) in order to know all the properties we have ever managed or about to start managing their contract. This helps measure the growth year over year.

I just realized a problem though and it has to do with a property being listed twice in my sheet and some being counted twice. That is because if a property ever sells, I retain the original row of data from our original contract, and with your formula assistance, it will go to 'inactive' automatically once their cancel date passes-I also make that original row a child row. Then I make a second row for that property (and make it a parent row) and call it 'active' since it is a new contract. If we did retain the business-on the child row of data, I check a box that says "Contract Update". If we didn't retain it after the property sells, I do not check it. I also do the parent/child row for those so I can look at the history of previous contracts for that property.

In short, (or long now lol sorry!) I am trying to put together a countifs that counts the properties that have "active" and "onboarding" status, and only count inactive that are not check for 'contract update'. The only status the 'contract update' box may be checked is when it is inactive. Active and Onboarding it remains unchecked always. I also want this to lead to a sumifs that totals up unit count across all properties using the same parameters. My headers are "Property" (text), "Property Status" (text based on parameters you previously helped me with) and "Contract Update" (checkbox).

Thank you so much in advanced for any help here! I am going to keep chippin away.

Shelton Alves

This is where I am on the sumifs but I keep getting unparseable:

=SUMIFS(Units:Units, [Property Status]:[Property Status], OR(@cell = "active", @cell = "onboarding", @cell = "Inactive")), [Contract update?]:[contract update?],false))

Shelton Alves

Hey Paul! Sorry-I have continued to tinker with it and kept coming up with other ways I could do it but still feeling stuck. I did slightly adjust how I am tracking things, but I still want to ensure I am not counting properties twice when I am summing up their units or counting the properties. This is what I tweaked:

Property Status (Active, Inactive, and Onboarding), Contract Update-which is a check box, and Status Change Reason which is a single choice drop down of "Contract Update", "Ownership/Management Change", "Straight Cancel"

I want to count the units in my sheet when:

'Active', and 'contract update' column is not checked, onboarding (count all in this status), and 'inactive' I only want to count when any of the three status are chosen of "Contract Update", "Ownership/Management Change", "Straight Cancel". Also note, if a property is "Active" the "Status Change Reason" column is blank. It is only filled in with one of the three choices when "Inactive" is chosen. My goal here is to get a true total of the units we have managed currently, previously, and coming up without counting any twice.

I am hoping with adding the 'contract update' checkbox, and only checking it on active rows, it will prevent me from counting the property twice. As I mentioned in my original post, I keep the old property listed and make it a child row to retain the data history from previous contracts and I make the current property listing as the parent row.

Still coming up short with the formula though :/

Paul Newcome

IF I understand you correctly...

You were actually pretty close with your SUMIFS. We actually want to use a COUNTIFS instead though because we are counting instances and not summing a set of numbers.

You also had two extra closing parenthesis tucked in there which is why you were getting the #UNPARSEABLE error. You only need one to close out the OR and one to close out the overall function.

=COUNTIFS(Units:Units, [Property Status]:[Property Status], OR(@cell = "active", @cell = "onboarding", @cell = "Inactive"), [Contract update?]:[contract update?],false)

Shelton Alves

Hey Paul-thank you so much for your help, I know I threw a lot in there. I wanted to share a snip of the columns and you can imagine each line is a property.

I want to sum the unit total for the history of the sheet for properties we managed past, present, and future. You can see I have one example of the checkbox being checked for 'contract update' (Property G) - I will makr a parent/child row to track property history when/if contract updates. All the other inactives are properties we truly no longer manage. I want to sum the unit total for the history of the sheet for properties we managed past, present, and future (based on property status). To do that, and not count a property twice-I believe I need to Sum the following based on the above columns:

Sum Active Properties with no check box in 'contract update' column. Count all 'onboarding' listed properties (those are future properties), and count inactive when any of the 3 choices from the dropdown are chosen (Contract Update, Straight Cancel, and Ownership/Management Change). By not counting the 'active' with a check for 'contract update', it should omit the units for that row, but count it from the 'inactive' line. Does that make sense? So instead of the overall total counting Property G as 47 units+47 units = it just counts the 47 units once for the overall sum total for all units.

Shelton Alves

Hey Paul-I thought through it. If I ever have a property with multiple child rows, then it would count each inactive line which would also count properties twice which I don't want :/

What if a parent row is present, it counts that parent row and ignores the child rows in the summing? As there will always be one parent row. I would still need to count 'all onboarding' and inactives that are non-child rows. As there will always be one parent row. That could help check a box if a row is a parent row and I could ignore the 'contract update' column because that could act as that. I may also be over complicating this and may need to think through it more to ensure items are not being counted twice when it comes to summing units or counting properties

Paul Newcome

My apologies. Not enough coffee yet this morning. Haha.

Let me make sure I have this right. You want to SUM the following types of rows:

[Property Status] = "Active" and [Contract Update] = Unchecked

[Property Status] = "Onboarding"

[Property Status] = "Inactive"

Basically you want all rows summed where [Contract Update] is unchecked?

Shelton Alves

No you are totally fine 😃 I just poured my second cup.

At first yes-but then I realized if I do that, and I have the same property listed multiple times as inactive (because there is a history of a new contract for that property), it would count the same property multiple times on the inactive side. So in my screen shot example-property G could potentially be listed as inactive multiple times, but I only want it counted once in the unit sum (or property count, etc). For what you mentioned on Active, unchecked and Onboarding that is good. Its the inactive that has the potential to be tricky as I am analyzing this. I am open to adjusting checkboxes. What if we do a sumifs for units and countifs to count properties if there is a checkbox that automatically checks when a row is a parent row? There will only ever be one parent row in my sheet, and multiple child rows of same property. Then maybe the formula can check against that automated parent row check box? Parent row would always be an active property too and child rows would always be inactive. Just trying to think through of better definitive ways to capture this.

Paul Newcome

What are the chances you are going to have

[Status Change Reason] = "Contract Update" and [Property Status] is NOT "Inactive"?

Similarly... What are the chances you are going to create child rows for anything other than "Contract Update"?

Shelton Alves

Great question and thank you for helping me think it through.

First question-zero

Second question-zero

The only thing with the first one, is there may be multiple property Gs that are inactive with child rows that could have "contract update" noted. Then if the parent row - if that active property every truly canceled and wasn't a contract update and we no longer manager it and becomes inactive as of the cancellation date(which is that formula you helped me with), I would want to make sure thinking through this formula captures that. Does that make sense? So a parent row could become inactive.

Paul Newcome

A parent row could become "Inactive", but would that same row also contain "Contract Update", or would it be listed as one of the other reasons with all of the child rows being the "Contract Update".

Here's why I am asking. I think we may be going about this the wrong way. Instead of trying to sum everything that fits all of these different criteria, what if we just summed everything EXCEPT FOR "Inactive/Contract Update" rows?

Shelton Alves

Hey! Sorry for the delay. If a parent row did become inactive, but it would be for another reason not related to contract update since we didn't keep the property-so it would be 'ownership/management change' or ' straight cancel'. Is there value in keeping a checkbox for the parent row that has 'contract update' box checked? I only recently added it, but its because an active property will have a blank "Status Change Reason', and in order to run a report for active properties that have a contract update-that is why I added it.

All that to say-I think that is a great idea! I don't think I have done much on formulas that have an EXCEPT for function. I like your thinking!