Reference List of Dates in Column Relative Today
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
Comments
-
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.
-
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
-
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.
-
What exactly are you attempting to do?
-
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
-
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?
-
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
-
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.
.
-
Happy to see that you got Zapier working!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
Happy to help!
Waiting for task count reset!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!