# Dated If function

#### CRUTHERFORD

##### Board Regular
Hi,

So i am trying to compose an excel formula that will do the following...

example:
Cell A1 = 15th March 2014
Name of Columns B = Bookeddate
Cells b1, b2, b3 etc all contain a bookeddate.
Name of Columns C = DepartureDate
Cells c1, c2, c3 etc all contain a DepartureDate

Is there a formula that can tell me whether the date in cell A1 falls between any of the dates in cells B1 + C1 or B2 + C2 or B3 +C3 etc as a range ?

Thanks,
Cam

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### barry houdini

##### MrExcel MVP
Try using COUNTIFS like this:

=COUNTIFS(bookeddate,"<="&A1,DepartureDate,">="&A1)>0

You get TRUE if A1 is within any of the ranges

#### CRUTHERFORD

##### Board Regular
that works thanks, but is there any way i can get it to return a cell value rather than TRUE or FALSE?

#### barry houdini

##### MrExcel MVP
You can just use that same test in an IF function, e.g.

=IF(COUNTIFS(bookeddate,"<="&A1,DepartureDate,">="&A1)>0,A1,"")

that formula will return the A1 value if TRUE or a blank if FALSE, change as required

#### CRUTHERFORD

##### Board Regular
Thanks

Just out of interest, if i added an additional column to the Bookeddate and Departuredate and named it 'Partner'; how could i get the formula to return the partner name linked to the correct bookeddate and departuredate?

cheers,
C

#### mrmmickle1

##### Well-known Member
Maybe you could combine it into something like this:

Code:
``````=IF([COLOR=#333333]IF(COUNTIFS(bookeddate,"<="&A1,DepartureDate,">="&A1)>0,[/COLOR][COLOR=#ff0000]A1[/COLOR][COLOR=#333333],[/COLOR][COLOR=#0000ff]""[/COLOR][COLOR=#333333])=TRUE, [/COLOR][I][COLOR=#ff0000][B]FIND PARTNER FORMULA HERE[/B][/COLOR][/I][COLOR=#333333], "NO MATCH")

[/COLOR]``````
Where I have typed FIND PARTNER FORMULA HERE you could probably enter an INDEX/MATCH FORMULA Formula based on the row the data appears on or maybe some kind of an OFFSET FORMULA

Last edited:

#### barry houdini

##### MrExcel MVP
OK, I'd probably use a different approach if you actually want to retrieve a value from the relevant row

Try using LOOKUP like this

=LOOKUP(2,1/(bookeddate<=A1)/(Departuredate>=A1),Partner)

That will return the Partner from the same row as the matching date range.

If there isn't a matching date range that will return an error so you can add an IFERROR function to capture that, e.g.

=IFERROR(LOOKUP(2,1/(bookeddate<=A1)/(Departuredate>=A1),Partner),"No match")

Replies
16
Views
717
Replies
13
Views
2K
Replies
2
Views
345
Replies
4
Views
527
Replies
11
Views
446

1,190,856
Messages
5,983,242
Members
439,833
Latest member
CDaviess

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back