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.

Not sure why I'm getting Invalid Data Type

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

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))

Comments

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

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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

This discussion has been closed.