Compare date cell to two date columns

[bkpate]

Greetings!

I don't know if this is possible, but what I am needing to do is compare a date/time cell to two date columns. For example, I have a column titled 'Date Created'. If this falls between any value in the 'Start Date' column and 'End Date' column, I want to use an IF statement to note that original row. The 'Start Date' and 'End Date' columns are on the same row, but I want the first date/time column to compare against all the rows.

[Sam Capricci]

What you are describing sounds doable but is it possible for you to mock up a sample workbook with enough representative examples AND expected results so people can give you the appropriate formula?
use go advanced below this reply window then part way down click on manage attachments, then browse, upload, close and ok to upload a sample.

[ChemistB]

Can you upload an example workbook? I'm unclear about what you expect. Do you want to return the two dates it's between in columns to the right or which row that combo is on? What if the Date/Time in E4 (for example) falls within multiple other lines (i.e E4 = 5/15/2019 and A1:B1 = 5/10/2019, 5/20/2019 and A17:B17 = 5/13/2019, 5/16/2019)?
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bkpate]

I've attached the file with a demo before and after...

I tried this formula: =IF(AND(A5>=E:E,A5<=F:F),"On Task","Available"), but did not get the desired result.

[ChemistB]

I assume that Row 7 is not on task because it appears before the two dates that would bracket it in row 8?

[bkpate]

Right. The logic is > than start date and < end date, not >= start date, <= end date.

[ChemistB]

This formula in your sample data seems to work (In G2 copied down). If real data is more complex, give some examples.

Please Login or Register  to view this content.

[ChemistB]

A7 is the same as A8 but A8 is on task

[Sam Capricci]

since I worked on it, I'm proposing mine too as an alternative. This appears to work though I think some of your examples are not coded correctly...
=IF(SUM(COUNTIFS($E$2:$E$15,"<="&A5,$F$2:$F$15,">="&A5)),"On Task","Available")

Deleted the ARRAY part, it works without making it an array.

EDIT2: to get mine to work and return the same results as ChemistB's use this instead... =IF(SUM(COUNTIFS($E$2:$E2,"<="&A2,$F$2:$F2,">="&A2)),"On Task","Available") and drag down.

[bkpate]

Thanks so much! Worked like a charm!