Date formula to reference another cell

tina.walter70356

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!

Mike Wilday

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. 

tina.walter70356

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!

Mike Wilday

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

timclare

Hi, is there any way that you can reference dates in a cell within a formula in a column that isn't formatted as a date? ie. I'm trying to do a summary table of the number of items matching a criteria between a date range, and want to reference the date at the top of the summary table. But given I can't format the summary column with the formula results as a date column, it's impossible to use a date at the top of the column for a date reference.

Any help would be appreciated!

Genevieve P.

Hi @timclare

You can turn dates into text by adding quotes at the end of your formula... for example:

=[Date Column]@row + ""

The + "" will translate the date into text so it can display in a Text/Number column!

Chers,

Genevieve