Return a date if the date falls between two dates

sg081994

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.

Sameer Karkhanis

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)

Sameer Karkhanis

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)

sg081994

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"

Sameer Karkhanis

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".

sg081994

Thanks @Sameer Karkhanis it worked! I had to change the data type of one of the columns. Thanks again!

Sameer Karkhanis

Glad it worked. Please mark your question as answered if you can.

ACarta

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.

Genevieve P.

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