Return a date if the date falls between two dates
I have two sheets -
Sheet 1 - this includes "Facilities Cycle start date" and "Facilities Due date (EOD)"
Sheet 2 - includes "Request date Simplified"
I need to write a formula in Sheet 2 to get me the "Facilities Due date (EOD)" from Sheet 1 if "Request date Simplified" lies between "Facilities Cycle start date" and "Facilities Due date (EOD)". I have used a couple of different formulas like INDEX/MATCH/COLLECT. I am trying to get the "Facilities Due date (EOD)" in Sheet 2 in the column "Facilities Due Date"
Please note that the actual request date is a auto created when items are entered, the "Request date Simplified" column is a function to simplify the auto number date format.
We have tried using this solution but its hard to refer to what the exact columns are - https://community.smartsheet.com/discussion/69648/index-match-or-vlookup-for-date-range. It is giving the Invalid column value error which indicates that column types are different.
Best Answer
-
Try this: (Please make sure the Facilities Due Date column in Sheet 2 is of Date type)
=INDEX( COLLECT({Sheet 1 Facilities Due Date EOD Range}, {Sheet 1 Facilities Cycle Start Date Range}, <= [Request Date Simplified]@row, {Sheet 1 Facilities Due Date EOD Range}, >= [Request Date Simplified]@row) , 1)
Answers
-
Try this: (Please make sure the Facilities Due Date column in Sheet 2 is of Date type)
=INDEX( COLLECT({Sheet 1 Facilities Due Date EOD Range}, {Sheet 1 Facilities Cycle Start Date Range}, <= [Request Date Simplified]@row, {Sheet 1 Facilities Due Date EOD Range}, >= [Request Date Simplified]@row) , 1)
-
Thanks @Sameer Karkhanis but its giving me an "invalid column name" error.
Range 4 is the "Facilities Due date (EOD)"
Range 6 is the "Facilities Cycle start date"
-
Are you sure that the error message is INVALID COLUMN NAME and not INVALID COLUMN VALUE?
If it says invalid name for some reason, then please check the column names used in the formula and actual name in your sheet.
If it says invalid value, then as I had mentioned make sure that the column where you are using this formula (in your case I assume it is "Facilities Due Date" in Sheet 2) is also of type Date. Another column that should also be of Date type is the "Request Date Simplified".
-
Thanks @Sameer Karkhanis it worked! I had to change the data type of one of the columns. Thanks again!
-
Glad it worked. Please mark your question as answered if you can.
-
I can't seem to get it to work. I'm so frustrated I can't figure it out. I've tried everything but throwing my laptop across the room :( Can someone please help me? I want it to return "Week 1" or whatever # week it is with where the circled date falls into.
-
Hi @ACarta
Since you're working in the same sheet, you'll have different references than the formula above.
Try something like this:
=INDEX( COLLECT([Primary Column]:[Primary Column], [WK # Start]:[WK # Start], <= [Wk/Date Represented]$1, [WK # End]:[WK # End], >= [Wk/Date Represented]$1) , 1)
The Collect function acts as a filter. You first list the column you want the value to be returned from (your Primary Column), then you list each column and criteria afterwards.
Your criteria in this case is locked to a specific cell in your sheet (row one - $1)
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!