Extract a value if today is between start and end date
Hi all,
I've been trying to wrap my head around this and I have trouble trying to self serve to find an answer (or it's just user error on my end haha)
I have a schedule with 3 main columns - the name, the start date and the end date. What I was hoping to do was extract who is currently scheduled if today was equal greater than the start date and if today was less than the end date. It seemed easy enough in my head but I may be doing something wrong.
Anyone have any suggestions?
Best Answer
-
Is it possible for overlap?
=join(collect(name:name,[due date]:[due date],@cell <= today(),[end date]:[end date],@cell > today()), ",")
Answers
-
Apologies, here's the formula I was trying to use but it doesn't work:
=INDEX(Name:Name, (MATCH(IF([Due Date]:[Due Date] <= TODAY(), IF([End Date]:[End Date]) > TODAY()), 0)))
-
Is it possible for overlap?
=join(collect(name:name,[due date]:[due date],@cell <= today(),[end date]:[end date],@cell > today()), ",")
-
Well you can create a new column and apply this formula to that:
=IF(AND(TODAY() >= [Start Date]@row, TODAY() < [End Date]@row), "Name@row", "")
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ -
@Amit Wadhwani That would only pull the name for that single row. L@123's solution allows the name(s) that meet the criteria to be pulled to the same cell which would keep you from having to scroll the sheet looking for a name.
-
@L@123 That worked! Thank you so much for the help
Appreciate everyone else's input as well :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!