Problem with date cell being blank

rkw

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

SmSulli

Randy,

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

SmSulli

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

 

Jason Anderson

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'

rkw

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

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