Automate Harvey Balls Based on Date in a Column

Hello,

This may have been asked previously however I cannot seem to find my specific request that only reference if a date is entered (not range related) so here goes. I have a Status column on a sheet and have it set to symbol: Harvey Balls. What I would like the sheet to do is to automatically insert the correct Harvey Ball based on if the reference column that has a date (any date) or not. Leave the cell blank if no date, Harvey Ball if any date is entered in the column. Assuming an IF formula would do the trick but need help with that.

Initial Call = Empty

Initial Presentation = Quarter

Survey = Half

Final Presentation = Three Quarter

Conversion Scheduled = Full

Find more posts tagged with

Formulas

Comments

Adam Kinney

Attached an image showing date fields populated and the associated Harvey Ball I would like to automatically appear in the status column.

Shaine Greenwood

Hi Adam,

I believe that you're looking for a formula like the following:

=IF(ISDATE([Conversion Scheduled]@row), "Full", IF(ISDATE([Final Presentation]@row), "Three Quarter", IF(ISDATE(Survey@row), "Half", IF(ISDATE([Initial Presentation]@row), "Quarter", IF(ISDATE([Initial Call]@row), "Empty")))))

This formula will reference cells on the same row (hence the @row variable) and will set the harvey ball status based on whether a date exists in the respective cell.

Adam Kinney

Thank you. Returning a #UNPARSEABLE error. saw Survey was missing brackets but still returning the same error.

Adam Kinney

@Shaine Greenwood got it to work using:

=IF(ISDATE([Qualified Win]@row), "Full", IF(ISDATE([Final Presentation]@row), "Three Quarter", IF(ISDATE([Survey]@row), "Half", IF(ISDATE([Initial Presentation]@row), "Quarter", IF(ISBLANK([Last Activity Date]@row), "Empty")))))

Need to add one more statement to cover [Verbal Agreement]@row the new column layout.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.