Error w/ Formula (#INVALID)

raymond.j.riosiii94806

Hi Formula Friends -

Can you help me identify what's going wrong with the following formula (Reference Cell: 'Count1'): =COUNTIFS([Work Related Exp]:[Work Related Exp], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2))

I was working a few days ago; however, something changed and I can't seem to figure it out. I have the exact same formula in another cell (Reference Cell: 'Count2'): =COUNTIFS([Source Patient]:[Source Patient], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2)) and it works just fine.

See below for more info (Note that some fields are collapsed or hidden to protect the data):

Thanks for any insight.

David Joyeuse

Hi @raymond.j.riosiii94806

Some more infos regarding the error returned would be heplful. Which INVALID error is it?

I suppose it's INVALID DATA TYPE, INVALID OPERATION or VALUE are unlikely.

Now Cell Count2 is the one returning the the error, not Count1. So that got me puzzled as well...

One more point, your DATE range looks at the column Created, but not the automated one. Thus DATEONLY has no use if you're working on this column. (I guess there's the formula: =DATEONLY([Created (Auto)]@row within it...)

I would suggest you to replace DATEONLY(@cell) with ISDATE(@cell) to just count cells that do have a date in it. It's quite possible problem comes from the PENDING parent row that has no date, though that wouldn't explain why it works on one formula and not the other.

Hope it helped!

Paul Newcome

@David Joyeuse If it was the DATEONLY function throwing the error because of the blank row, shouldn't that also throw an error in the formula that is working? Additionally... I'm not sure how you would incorporate an ISDATE function into the formula since the dates are being compared to a set of specific dates?

@raymond.j.riosiii94806 Let's start with looking at what is different between the two formulas...

How are the [Encounter Date]$2 and [Date Resulted]$2 populated?

Also check your [Work Related Exp] column to see if that same error is present.

raymond.j.riosiii94806

The error is #INVALID DATA TYPE.

I have the formula pulling from the column Created because the automated one operates in CST (We live in PST) and by doing this, the premature dating issue is resolved.

@Paul Newcome:

[Encounter Date]$2 is a formula: =VLOOKUP(Name2, {COVID Metrics_DateRange}, 11, false)

The vlookup references another sheet which pulls in the starting date range based on the week number: =WEEKNUMBER([Encounter Date]2) - 1

[Date Resulted]$2 is a formula: =[Encounter Date]3 + 6 (gives me the date range I'm looking for)

Paul Newcome

Ok. That leads to a few more questions...

In your screenshot are we seeing rows 1, 2, and 3 at the top? I ask because if that is the case then [Encounter Date]3 is blank in which case [Encounter Date]3 + 6 shouldn't be returning a date.

Can you explain this in more detail? How are you generating a date from a weeknumber?

"The vlookup references another sheet which pulls in the starting date range based on the week number: =WEEKNUMBER([Encounter Date]2) - 1"

What exactly is in the range {COVID Metrics_DateRange} and how exactly is that populated?

raymond.j.riosiii94806

@Paul Newcome - thank you for responding. See below:

Yes, in the screenshot, you are seeing rows 1, 2, and 3 at the top. Typically, the "Encounter Date" and "Date Resulted" fields are manually entered when the Source MRN (patient) is entered into the sheet.

Encounter Date1 is a formula: =TODAY().

Encounter Date2 is a formula: =VLOOKUP(Name1, {COVID Metrics_DateRange}, 11, false).

There is also another formula in Name1: =WEEKNUMBER([Encounter Date]1) - 1. This is what Encounter Date2 is using as the vlookup on a separate sheet (the reference for that sheest is: {COVID Metrics_DateRange}, screenshot below:

Paul Newcome

So the only real difference is going to be the [Work Related Exp] column vs the [Source Patient] column. Hmm...

Did you check every single row in that column to see if there was that error? Do you have any values in that column aside from flagged vs unflagged?

raymond.j.riosiii94806

Yes, that's correct. I also don't understand why the problem on exist in Count2 and not in Count1. Again, it worked in the past and I didn't change anything (as far as I know).

No other values aside from flagged/unflagged.

Paul Newcome

Ok. Try this...

Remove the broken formula. Log out. Clear your browser's cookies and cache. Log back in. Manually retype the formula back in.

raymond.j.riosiii94806

@Paul Newcome - Yeah, that didn't work. I also attempted to fix via another browser to no avail.

I really don't understand what the problem with the formula might be.

Perhaps someone from Smartsheets Technical Support team can help?

Paul Newcome

Yes. I would suggest reaching out to Support at this point. Please revisit this thread once you have found the cause/solution and let us know!