Reference List of Dates in Column Relative Today

hildegrant

Hello! 

I have a project I am working on where I've created a little bit of a manual schedule that I'm integrating into a smarter excel sheet, but I'm struggling with a formula to duplicate information. 

I've attached a screenshot below of my spreadsheet... what I need to have happen is this: 

I would like to have a formula reference column "Date" and search for the date that is within 3 days of today's date (auto update as a week goes on), and place the applicable date from column "Date" into a specific cell in Column "1". I feel like I'll need to use the FIND command, but its asking me to reference text instead of a date, and I'm not able to get a search for this data to read cleanly. 

Any feedback or assistance would be greatly appreciated!

Thanks!

Grant

 

Paul Newcome

If you are trying to populate the date for Monday of the current week, change the column to a Date type column and just type in "Mon" without the quotes.

hildegrant

Paul, 

Not exactly what I am trying to do, but it's given me an alternative work around idea. Is it possible to me to have a cell update to the Monday of a current week over time? If I type Mon, it will show me monday's date for the current week, but I'd like that cell to auto update to next Monday next week without me needing to type Mon again.

Thanks!

Grant

Paul Newcome

That is exactly how it works. It is basically leveraging the TODAY() function in the background to give you the Monday of the current week based on TODAY(), not the date that it is entered.

Paul Newcome

What exactly are you attempting to do?

hildegrant

I have a master sheet referencing specific cells on this sheet to grab the hours that a specific user is needed for. Another screenshot is attached here... In short, I need the data circled to be duplicated in the empty circle for the future week. So today (8/15/2019), I want the data for the 8/19/2019 row to duplicate over. Then, starting on 8/20/2019, I want it to automatically change to duplicating the 8/26/2019 data. 

Does this help? 

Thanks, 

Grant

Paul Newcome

Ah. Got it. So you want the Monday AFTER today. Which means you want the earliest date of everything from the list that is larger than today.

 

So we are going to COLLECT all dates within the range of Date4 through Date15

 

=COLLECT(Date4:Date15, 

 

on the condition of the dates within that range

 

=COLLECT(Date4:Date15, Date4:Date15, 

 

are greater than today.

 

=COLLECT(Date4:Date15, Date4:Date15, @cell > TODAY())

.

We then want to grab the earliest (or the MIN) from the dates we collected.

 

=MIN(COLLECT(Date4:Date15, Date4:Date15, @cell > TODAY()))

.

From there a basic INDEX/MATCH using the date as reference will pull the corresponding data. Are you familiar with INDEX/MATCH formulas which are basically a much more flexible and robust version of the VLOOKUP?

hildegrant

THANK YOU PAUL! That is exactly what I needed for the date to fill in. Then it will update each week as long as I "touch" the sheet, correct? 

I also got the INDEX/MATCH to work, so I'm all set! Appreciate the help today!

Thanks, 

Grant

Paul Newcome

As long as the sheet is somehow activated. Whether that be from directly opening it, a form submission, a cell link or cross sheet reference updating, a 3rd party too such as Zapier sending a "ping" to the sheet. It doesn't HAVE to be opened. Just activated.

 

I personally use a combination of a cell link and Zapier.

 

I have a sheet that has nothing more than =TODAY() in a date cell on it. I use Zapier to add a new row daily (which can be done on the free plan) to that sheet which updates the TODAY function without me having to open the sheet.

 

I then will create a cell link to that particular cell on any sheet where I need to use the TODAY() function and reference the linked cell in my formulas. Usually I just create a column called "Today" and link it in row one. Then instead of having =TODAY() in a formula on the sheet, it is =Today1. The Today1 cell automatically updates every time its source cell updates, so with minimal effort I now have an automatically updating TODAY function that I can use across any sheet in any formula.

.

 

 

 

Andrée Starå

Happy to see that you got Zapier working! 

Paul Newcome

I THINK I did. Haha.

 

I have to wait until my monthly tasks counts resets. Until then I am going into the zap and telling it to send another "test". Once my count resets I will be able to turn the zap on to see exactly how it is actually working, but after reading through everything I am 99% certain I have it set the way I need it to be. Lol.

 

Thanks again for your help with that.

Andrée Starå

Happy to help! 

Waiting for task count reset!