Formula to count a number of dates, based on a date range.

Yegor M

Im trying to create a formula for a report based on howmany entrys have been created within specific date ranges. 

 

For example: how many entrys have been created 04/15/15 - 04/20/15 in column "date of entry" (auto number column).

 

Please help!

Best regards, 

Travis

Eager - This can be done using a Checkbox column and a couple cells in a Date column used for reference. Here’s how to set this up:

 

-Insert a Checkbox column into your sheet

-Add this formula to each checkbox cell:

 

=IF(Created4 >= $Date$1, IF(Created4 <= $Date$2, 1))

 

“Created” is your System Column (date of entry) and “Date1” and “Date2” contains the date ranges you want to count. You can’t add dates to an IF statement so you need to reference Date cells that contain the dates.

 

-Add this formula to a Text/Number column to count the number of checkboxes ‘checked’. I will call the Checkbox column “CB” in this example:

 

=COUNTIF(CB:CB, 1)

 

This formula will count the number of times the box is checked in the CB column which are checked if the corresponding date is within the range you designated in the two Date cells. 

Yegor M

Travis, 

Thank you for your reply, it defiantly makes sense on a single date range, however how would this update live on a daily basis? The single column will only create a check mark based on a single date range formula and the report is viewed weekly.  Would i have to create a column for each week (53 weeks in a year)? 

 

 

Any suggestions on Smartsheet apps, that allows to run graph chart based on dates?

 

Best regards, 

Travis

Hi Yegor - you are correct, this would show only a single date range. However, as your weeks progress you can change the two dates that the formula is referencing to the new date range. Changing just these two dates will give you a new count of the new range and won’t require you to change the formulas in your Checkbox column or to add a new Checkbox column. If you want to compare one date range to another, you could add the results to a section of your sheet each week. With this method, you formula would show your current week’s count, then a separate section would have the running totals of previous weeks. 

 

If you want a live updating range - for example to show you a count of the last 7 days - you could add formulas to your date range cells to automatically show that range when you open you sheet. Let me know if you need help setting that up. 

Rianna Ramirez

Re: Travi's Comment 

 

If you want a live updating range - for example to show you a count of the last 7 days - you could add formulas to your date range cells to automatically show that range when you open you sheet. Let me know if you need help setting that up. 

 

Can you send me the formula I need to generate the last 7days? I already followed everything on this thread and it works. I just want the formula to generate the count for the last 7 days. Tnx!

System

Travis- I seem to need what you are explaining above, but don't fully understand your second post on how I change just two dates without the need to change the full checkbox column formula etc..

 

Thanks!

Allison Vess

Hi Travis. Yes, I am responding to a post that's 2 years old!  I tried your formula since I too would like to count dates in a certain range.  I created my checkbox column and put this formula in every cell

=IF("Exit Date" >= 5 / 1 / 17, IF("Exit Date" <= 5 / 31 / 17, 1))

The column for my dates is called "Exit Date" and until I put it in quotes I was receiving the dredded "nonparsable" error messages.   So I'm no longer receiving error messages, but the formula isn't working for rows where the exit date is between 5/1 and 5/31 - it's not checking the box.

Can you help?