Not sure why I'm getting Invalid Data Type

lastoneover

Hello!

I'm trying to check a box to send an notification to our PM if: 

1. column A [Best Day and Time for Service] is less than or equal to 7 days away

2. Column B [1st Visit: Date] is blank

AND...if column A is blank, to leave the box unchecked.

When I put a date in Column A, it returns "invalid data type" in my column with my checkbox, where I ran my formula. 

It seemed to be working fine before I added the first ISBLANK phrase. 

Can you help? Thanks!

 

=IF(ISBLANK([Best Day and Time for Service]11), 0, AND(ISBLANK([1st Visit: Date]11), [Best Day and Time for Service]11 - TODAY() <= 7, 1, 0))

J. Craig Williams

Oddly, I do not get an #INVALID DATA TYPE error. Make sure your two columns are Date type. 

That said, your formula is malformed.

What is say is

if the Best Day is blank, then 0

otherwise this (which is OK since it returns a Boolean value)

AND(ISBLANK([1st Visit: Date]11), [Best Day and Time for Service]11 - TODAY() <= 7, 1, 0)

I think this may do what you desire:

=IF(ISBLANK([Best Day and Time for Service]23), 0, IF(AND(ISBLANK([1st Visit: Date]23), [Best Day and Time for Service]23 - TODAY() <= 7), 1, 0))

for row 23. You just need to add the 2nd IF statement and close the parentheses

I hope this helps.

Craig

 

 

J. Craig Williams

Advanced Users:

Since the formula resides in a check box column, this also works:

=AND(ISBLANK([1st Visit: Date]23), [Best Day and Time for Service]23 <= TODAY(7), NOT(ISBLANK([Best Day and Time for Service]23)))

1. No IF because AND() return a Boolean value (which is what the check box is expecting)

2. Uses <= TODAY(7) instead of - TODAY() <= 7 as TODAY takes a integer value for +/- days from today.

Craig