Best Of

Hello @CatB

It looked like your Date column has either an empty cell or a non-date value so you need to account it using an IFERROR function. Since having either an empty cell or a non-date value will throw an error and that causes the #INVALID DATA TYPE error message.

Try this:

=COUNTIF([Date Opened]:[Date Opened], IFERROR(MONTH(@cell ), 0) = 1)

Hi @gmb-nps-xofigo88916

Yes, we can create a text field that appears in the format you described. Your mo/yr column should be formatted a text/number column

If the leading zero for the month isn't important, the equation is:

=MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row), 2)

*be sure I got the name of your date field correct

If the leading zero of the Month is important, then this formula should work

=IF(LEN(MONTH([receipt date]@row)) = 2, MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row[receipt date]@row), 2), "0" + MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row), 2))

If you want the year to be 4 characters then omit the Right function.

=MONTH([receipt date]@row) + "/" + YEAR([receipt date]@row)

cheers,

Kelly

The format for an if() function is:

=if(Test, Value if True, Value if False)

Your test section was written as: INDEX({CT Certs Range 1}, MATCH([Instructor Name]@row, {CT Certs Range 1}, 0))

This tells Smartsheet to index through your {CT Certs Range 1} by a variable number of rows (in this case, by the number of rows necessary to find the name from [Instructor Name]@row). What is DOESN'T do is give any test to be evaluated as true or false. Smartsheet finds some names, indexes through them and chooses the first, but then your "test" is just that name… hence, trying to decide whether "Frank" is true or false.

For the test section you need to include a comparison operator (the official documentation is quite poor on this, btw). Here is the list for Excel (there is no official list for Smartsheet):

So, for your test you need to use one of these so it could evaluate to either true or false. You could have done something like:

=IF(INDEX({CT Certs Range 1}, MATCH([Instructor Name]@row, {CT Certs Range 1}, 0))=[Instructor Name]@row, "yes", "no")

and it would likely have also worked. In this case, the formula is saying "Go do that indexing and grab the first name, if it matches with the name over in this other column consider it true and do the following…. otherwise….."

You see, it is the "if it matches…." bit that was missing before and that adding "=[Instructor Name]@row" to the "test" portion of the formula adds.

Alternatively, in the formula I recommended to you, you can see that I tell it to get the count but then the test is actually "if that count is greater than 0".