Best Of
Re: What column type is required to use MONTH to return number of new lines in a certain month?
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)
Melissa Yamada
Re: formula to show month and year based off a date xx/xx/xx
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
Kelly Moore
Re: March Question of the Month - Join the conversation and receive a badge
I would like to improve my guitar skills. I've been playing for many years, but I'm still very much a chord strummer. I can play difficult things when I sit down and apply myself. I'd like to apply myself more often so that if I'm invited to jam with others, I can carry my part without hesitation.
Emmett Tomkinson
Re: IF INDEX MATCH yielding INVALID DATA or NO MATCH
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".
Jgorsich
File Library: Single File Sharing Now Available!
We’re excited to announce the release of single file sharing for file library! Users can now quickly and securely share individual files with anyone, whether they’re already a part of your Smartsheet account or not, without granting access to the entire workspace. Maintain project oversight and control of your critical content by knowing exactly who has access to your files. Learn More
Environment Availability: Commercial US
Plan Availability: All plans
Subscription Model Availability: User Subscription Model
Karin Maltrud
Re: Dashboard - Dividers
I uploaded a bunch if you want to grab some for your use here
Is there a way to add a thin vertical line to a dahsboard? — Smartsheet Community
Here are a few i showed in action. Just go to the link above and you will see my post with the attachments. You can always reply here if you want more or grab the handful in the link above
Julie Becker
We’ve made improvements to your copy and save experiences
Good news community, we’ve updated the speed of our Save as New performance, so now you can expect better, more reliable save behavior, especially for large folders and workspaces.
We’ve also made improvements to the dashboard copying experience. Specifically, we have enhanced form relinking in dashboards, a feature that began rolling out in a prior release, to make it more seamless. Now, when you copy or save from a folder containing a dashboard with an embedded form, the copied dashboard will automatically link the new form with the newly created sheet. Previously, the form link would continue pointing to the original sheet, requiring manual updates. This enhancement eliminates that extra step, ensuring copied dashboards function correctly right away. To learn more about this, you can reference the help article here.
Note: We will roll this out to all users over the next several weeks.
Environments availability: ,Commercial US
Plan availability: All
Subscription Model Availability: Both
Stay informed by Subscribing to receive product release updates for curated news of recently released product capabilities and enhancements for the platform of your choosing, delivered to your inbox. As new releases occur, you will receive a weekly email with news of what's released every Tuesday.
Thank you,
John Hieger
Product Marketing
John Hieger
Re: Having issues with Year formulas
Does this work?
=IF(YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0) > 0, (YEAR([Date Completed]@row) - YEAR(Age@row) - IF(MONTH([Date Completed]@row) < MONTH(Age@row), 1, 0)) + " Y ") + (MONTH([Date Completed]@row) - MONTH([Date Completed]@row) + IF(MONTH([Date Completed]@row) < MONTH(Age@row), 12, 0)) + " M"
Paul Newcome