Dated If function

[CRUTHERFORD]

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

 

[barry houdini]

Try using COUNTIFS like this:

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

You get TRUE if A1 is within any of the ranges

 

[CRUTHERFORD]

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

 

[barry houdini]

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]

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]

Maybe you could combine it into something like this:

=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

 

[barry houdini]

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