On Thu, 19 May 2005 03:15:36 -0700, Sho wrote:
Can anyone help with the following;
I have dates in column B e.g. 01/05/05 (could be lots of dates on the same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;
If the time in cell C1 is the first time after 0800 on the same date if it
appears in column B, then I want to see the result of 1 appear in D1.
e.g.
Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640
Any help would be great!
Assumptions:
1. Data begins in B2
2. Range B2:B100 is NAME'd Dates
3. Range C2:C100 is NAME'd Times
Use the **array-entered** formula in D2:
=IF(AND(ISNUMBER(C2),C2=MIN(IF(B2=Dates,Times,9999 ))),1,"")
and copy/drag it down as far as needed.
To **array-enter** a formula, after copying or typing it into C2, hold down
<ctrl + <shift while hitting <enter. Excel will place braces {...} around
the formula.
The ISNUMBER function is to ensure there is data in Column C or else it will
also give a blank result.
--ron
|