Formula question

Stephanie Estes

Hi Smartsheet community! I realize there is a help area for this but I have looked ... read.. googled.. till I'm blue. So this is new and I thought in this NEW COMMUNITY maybe I could shout out for some help. I am trying to do a formula so that the smartsheet can look at a month ( column ) and then determine in another ( column ) the type of visit that's due based on that ( month ) .. 

 

So to explain more.. I have been manually pulling down weither a client has a Routine visit due or a 6 Month visit due. On all 500 clients.. My hand hurts! 

 

Is there a easier way?

 

Generally we put in the Start of care date.. and then from there we can determine in the ( OTHER ) column what type of visit on of my staff should be putting down. ( 6M or Routine ) this is getting tiresome. Is there not a formula that I can use? HELP!!!! 

Jeremy Michels

I need some more information on this to give you a better answer. I use a formula to do a similar thing.  We entered a column to be a trigger to enter in the info into the desired column. 

Martha Issa

As Jeremy mentions there is more info needed to help with an exact formula... Do ALL patients with more than 180 days, after start date, need a 6 month flag? Or could there be others with that same time lapsed that need to be 'routine'? 

If after 6 months all need a 6M then you can use something like this:

=IF((TODAY() - [Column1]1) > 180, "6M", "Routine")

 

Column1 = column where you have your Start Date

Chris Barushak

Stephanie - what's the required interval for the routine visits?

 

Martha did a really nice job of what I would do, in this case.  I've also built a separate sheet, to track customer complaint repsonse times against target, and you'll need what I call "helper" columns to help faciliate the correct formulas,

 

My email is cjbarushak@bemis.com, and I'd be more than happy to assist.

Jim Hook

As Martha already mentioned, I think a nested IF statement can solve your problem. If you have a column that calculates the number of days, for example, since the last visit you can use an IF statement to select which type of visit is due. Smartsheet has a good tutorial on nested IFs that explains how to test for multiple conditions (number of days for example) and return a different text string that matches the appropriate number of days since the last visit. It does this all in a single formula tha can get quite long. You could then use conditional formatting to highlight visits that are overdue so they would pop out at you on the sheet.

Stephanie Estes

First, thank you all!!! 

   Every client we have has a ratio of visits. Some are seen every 3 months while others are monthly. The start of care date is our starting point. It is a must that all clients get the normal 6 month visit. So for ALL clients this is a must. In addition we have to do routine visits. Some are done quarterly while others are done every 30 days. 

Martha that was awesome!

 

Anyone that can help assist with all three formula's we would ever so appreciate it! My hand welcomes the break!