3

Good Morning!

I'm seting up a metrics spreadsheet with a lot of formulas.  The person who will be administering it in the future isn't really familiar with formulas so I'd like to make it as easy as possible.  I created a formula that does what I need =COUNTIFS({Date Reported}, >=DATE(2018, 11, 26), {Date Closed}, <=DATE(2018, 12, 2)) 

Rather than include the actual date in the formula I would like to reference a table of dates in the same or a different sheet.  For example, is there a way to say =COUNTIFS({Date Reported}, >=DATE(Week 1, Start Date), {Date Closed}, >=DATE(Week 1, End Date))?

 

                  Start Date    End Date

Week 1    11/26/2018    12/02/2018

Week 2    12/03/2018    12/09/2018

Week 3    12/10/2018    12/16/2018

 

I'm thinking this would allow the administrator to maintain the dates for each week in the table versus dealing with formulas.  If anyone has another solution that would be great too.

 

Thanks for your help!

Functionality
Formulas

Comments

Yes, this is totally possible.

Make sure the column where you are entering the date is a Date Column type.

You would reference the cell by using its column title and cell number. But if the cell is already formatted as a date refrence you don't have to use the Date() formula, just reference the cell as in my attached screenshot. I used the column and cell refernce of Today1 (today was my column name... not the Today() formula. 

Anyhow you should be able to do your Countifs and then just reference the cell on your sheet that you set up, without using the DATE function because the cell is already formatted by the date column. 

Thank you Mike, that was the key to get me started. I knew it was something simple. 

I've included a screenshot for anyone else who may be trying to do something similar.  The formula works great!

You're welcome! Glad I could put you on the right track.