help with unusual date find

[saban]

Plizz someone help me out with this one:

I have date in cell A1 and Date in Cell B1 and another dates in cell A2 and B2

Now I will write date into cell C2 and check if this date is between dates in cells A1 and B1 or A2 and B2 or A3 and B3 and so on

Actually what I want to do is check for the date written no matter where in column C , to check if this date somwhere in column C is beetwen dates in A1 and B1 or A2 and B2... and so on

any Ideas
thnx

[Bob Phillips]

Use data validation with a formula of

OR(AND(C1>=$A$1,C1<=$B$1),AND(C1>=$A$2,C1<=$A$2))

[saban]

but it will always check cell C1 with every other cells (all A and all B's) It is ok
but I also need something to check each cell from C column not just C1

Thnx, will definetly try your method and let you know

[saban]

well I need that: in column C shouldnt matter in which cell date is written, if the date in column C is between dates in col. A and B it should check that
( for example : In cell A3 and in cell B3 dates are written- 21/05/2006 in A3 and 24/05/2006 in cell B3 and in cell C1 date is written -22/05/2006
Now I need column D to say "true" or "false" if date in C is in that range
Now if date in C1 is between A3 and B3 should say "true" else "false"
and so on, It shouldnt matter in which cell in C column date is it should find every date in columns A and B that coresponds to date in C column

thnx for everything

[saban]

all that I need is that every entry in column C is checked (if it exists between dates) with every entry from column A to column B

[Bob Phillips]

Select all the cells in column C that you want it to apply to, then add that formula. Excel will adjust for all other cells.

[saban]

Can you be plizz more specific why should I select all the cells in C I need answer false or true in D column in C are just dates which I want to compare with A and B

but will try what you suggested

[saban]

Can you plizz tell me what you meant with that data validation and formula?? I dont quite get what you mean

Thnx for everything

[Apps]

Hi Saban,

Include the Function code below in your workbook, and then on your worksheet use the formula:
=BETWEENDATES(Column1,Column2,###)
- where:
> Column1 is the letter of the first column to check (in your example 'B')
> Column2 is the letter of the second column to check (in your example 'C')
> and ### is the cell ref of the date you wish to check, (in your example 'C1').

This then checks every instance of B1 & C1, B2 & C2, B3 & C3, etc. for each Cell ref value and returns True or False.

[vba]
Function BETWEENDATES(Column1 As String, Column2 As String, DateToCheck As Date) As Boolean
'Column1 & 2 are Letters of Columns to Check e.g. "B" and "C"
Dim Date1 As Date, Date2 As Date
Dim xR As Long
'check each row ref in Columns 1 & 2
For xR = 1 To 65536
On Error GoTo xErr
Date1 = Range(Column1 & xR).Value
Date2 = Range(Column2 & xR).Value
'Check if Date is between Date1 & Date2
If DateToCheck >= Date1 And DateToCheck <= Date2 Then
BETWEENDATES = True
Exit Function 'if found, then exit function
End If
xErr:
Next xR
BETWEENDATES = False
End Function
[/vba]


Hope this helps

[mdmackillop]

Hi Saban,
I've pinched some of Apps code for the attached file, which I hope is what you're after.
Regards
MD

[saban]

thnx man will check it out and let you know
it is very close to what i need

I would need to say TRUE in row where actually record is not just telling me in which row
But even better would be (if TRUE) to decrease a value in let say D column :
for: where date is found in between dates in A and B check those two dates for networkdays(workdays) and divide workdays with 6 and then deduct value in D column for 6/networkdays

any ideas how to do that