Can Smartsheet Count Dates?

I have tasks in each month and want to count those tasks within each month in my metrics section. Here's my formula to calculate February tasks: =COUNTIFS([End Date]:[End Date], ">=02/1/19", [End Date]:[End Date], "<=02/28/19")

In Excel this works, in Smartsheet I get a 0, but have tasks within those dates. Help!?!

Find more posts tagged with

Formulas

Comments

L_123

=COUNTIFS([End Date]:[End Date],and(@cell >=date(2019,2,1), @cell <= date(2019,2,28

Give that a try. Smartsheet needs to be told the data type if you are dealing in dates.

steven.nienaber105336

That worked! THANK YOU!

Paul Newcome

The reason this works is because of the DATE function. The way you have your formula written with the quotes, the formula is looking for that very specific text to be a match to include the Greater/Less Than and Equals symbols.

If you just remove the quotes, then the formula would be looking for a match to the result of the mathematical equation of 2 divided by 1 divided by 19.

Using the DATE function lets the formula know that it is in fact looking for a date.

SkiPatrolScott

@Paul Newcome , I have a sheet to track number of avalanche routes we go on, so a single day may have 1-39 routes, each with the same date in [control date]. I want to create a Metric Dashboard that will track various statistics to each route.

To start, I want to display how many Days we have had to do avalanche mitigation, have tried various formulas but cannot figure out how to Summarize 11/15, 11/15, 11/5...11/15; 11/18, 11/18...11/18 to show "2 Control Days"

Any ideas?

Paul Newcome

@SkiPatrolScott You would need to incorporate a DISTINCT function.

SkiPatrolScott

Thanks @Paul Newcome and @Genevieve P. about 5 minutes after searching for 30 min, I found the Distinct formula and it seems to be working.

Still wish there was a mask input, but we are making workarounds. Once I get the sheets up with data I will share with you? just to see what you think?

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…