Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Problem with date cell being blank

Options
rkw
rkw
edited 12/09/19 in Archived 2017 Posts

I am thinking this should be easy but can't seem to figure it out and would really appreciate some help.  In working with our customers, we first create an estimate with the goal of turning it to a work order if the customer decides to make a purchase.  I have a column entitled 'Estimate Creation Date', and I want to have my 'Needs Attention' column checked if a certain amount of time has gone by and the job is still an estimate and we have not followed up with the customer.  Likewise, I have a column entitled 'Last Follow-up Date', and I also want a checkmark to show up in 'Needs Attention' if a certain amount of days have passed from the date showing in 'Last Follow-up Date'.  My problem is this...  There is no date in 'Last Follow-up Date' until we have followed up with a customer, and that is throwing off my formula, because 'Needs Attention' is getting checked even when my 'Estimate Creation Date' is today's date or something very recent.  The formula I have so far is this...

=IF(AND(ISBLANK([Last Follow-up Date]150), ISBLANK([Work Order Date]150), [Estimate Creation Date]150 < TODAY() - 21, [General Interest Level]150 <> "Decisive Selections Made"), 1, IF(AND(ISBLANK([Work Order Date]150), [Last Follow-up Date]1 < TODAY() - 21, [Building Phase]150 <> "Beginning"), 1, IF(AND(ISBLANK([Work Order Date]150), [Last Follow-up Date]150 < TODAY() - 45), 1)))

I hope this makes sense what I am trying to do.  Basically, as long as 'Last Follow-up Date' cell is blank, it throws off my whole formula, and I don't know how to resolve.  Can someone please help me with this?

Randy

Comments

  • SmSulli
    Options

    Randy,

    Take a look at your formula.  You've got one cell labelled as row 1, instead of row 150.  

    CommunityHelp.JPG

  • SmSulli
    Options

    Randy,

    Take a look at your formula.  You've got one cell labelled as row 1, instead of row 150.  

    Give that a try

    Shawn

     

    CommunityHelp.JPG

  • Jason Anderson
    edited 10/06/17
    Options

    General thought for the process:

    What if, when a row is added there is a formula in the 'Last Follow-up Date' column that by default just copies in the 'Estimate Creation Date'? Not knowing the full details of your process it would seem that you would create an estimate as part of a customer contact. I would then have a conditional formatting rule that would compare the two and if 'Estimate Creation Date' = 'Last Follow-up Date' it would get a light gray font, something to visually indicate that it's just the default. 

    If someone has contact with the customer they just change the 'Last Follow-up Date'. The 'default' formula is replaced with a date value.

    You could have a formula in the 'Last Follow-up Date' that copies the 'Estimate Creation Date'

    chrome_2017-10-06_09-20-57.png

    chrome_2017-10-06_09-22-09.png

    chrome_2017-10-06_09-22-52.png

    chrome_2017-10-06_09-23-41.png

  • rkw
    Options

    Thanks guys for your input.  I changed the '1' to '150' and still have the same problem, unfortunately.  I appreciate your suggestion, Jason, to have a default date populate that field when we do a new estimate.  I kind of wanted that follow-up field to be blank so we know at a glance if we really have followed up.  However, coupled with the conditional formatting you suggested, I think that should work just fine.

    Still curious if there is a way to accomplish what I was trying to do, but this is a good work around, and I really appreciate you both taking the time to look at this for me.

    Randy

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Be aware that manually adding data to a cell will remove a formula that resides there ... and any additional rows will not be auto-filled with the formula.

    There is a way to accomplish what you want. What I am lacking to fully help you, however, is an understanding of your process. You have a lot of columns listed in your formula attempt

    [Last Follow-up Date]

    [Work Order Date]

    [Estimate Creation Date]

    [General Interest Level]

    [Building Phase]

    and [Needs Attention] is where I believe the formula will reside.

    1. Some creates an Estimate .. manually enters a date in [Estimate Creation Date]

    Needs Attention = unchecked

    2. Time passes until

    Need Attention = checked

    How does the system know if it needs to check the box or not? What tells it that it is "still an estimate"? 

    And so on for each phase/stage/task in the process.

    If the process is linear, it is really quite straight-forward. I've built these 'need attention' sort of workflows dozens of times, some with as many as 15 stages, where the baton gets passed after some does something they are supposed to do.

    I think you are on the right track. A visual flow-chart is also very helpful when thinking about your process.

    Craig

This discussion has been closed.