Can Smartsheet Count Dates?

steven.nienaber105336

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!?!

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?