formula for giving "yes" if services are between certain dates

**anna57** · 11-11-2009, 10:12 AM

Hello!

I have what i think might be quite a complicated formula question... I have 6 columns that represent certain services (3 services in total) and dates on which they were taken up (see attached document). I would like the result of the formula to be "yes" if either of the 3 services have been given within certain date parameters (i.e. within the quarters specified in the green box also on the spreadsheet). Does anyone have any idea how this oculd be done?

I have kind of been doing similar things using a SUMPRODUCT function but I don't want this to actually count up the number of services, i just want the formula to say "yes" if any of the 3 dates are within a specific time frame.....thinking about it the services columns could just be completely ignored and the 3 date columns could be the only things used in the formula.....

Any ideas?

Anna

**zbor** · 11-11-2009, 10:21 AM

Look here:

example%20database%202(1).xls

I didn't make for third column.

1) you need to enter dates as dates
2) what if something is in 2 quarters?
like 9 and 10th row?

**anna57** · 11-11-2009, 10:38 AM

If dates on the smae row were in different formulas I would want "yes" (or what ever the alternative is) to be in the outcome for both quarters. Just a quick question about your amendments to the database, I see you have rearranged the quarters box, how does the computer know to look between certain dates? Does it know this by you highlighting the K2:L9 box?

Thanks so much for your quick reply!

Anna

**zbor** · 11-11-2009, 10:47 AM

Sorry, I forgout about your "yes" issue

Something like this?

=IF(VLOOKUP(B3;$K$1:$L$9;2;TRUE)=VLOOKUP(D3;$K$1:$L$9;2;TRUE)=VLOOKUP(B3;$K$1:$L$9;2;TRUE);"yes";"x")

Yes, vlookup functions:

=VLOOKUP(D3,$K$2:$L$9,2,TRUE)

D3 - what value to look

$K$2:$L$9 - within what range

Return n-th column for seeked value

TRUE/FALSE - find aproximate/exact value

Since you look aproximate it will all dates put in same Q until next date is reached (need to sort them in ascending order)