Based on what you wrote, I think you intended to put a 1 in the first row.
Try something like this in D2 and fill down to the end:
=IF((B2=B1)*(TEXT(C2,"00\:00")*1="08:00"*1)*(SUMP RODUCT(($B$1:B2=B2)*(TEXT($C$1:C2,"00\:00")*1="08 :00"*1))=1),1,"")
Then use this in D1 in case the first time value is greater than 8:00:
=IF(TEXT(C1,"00\:00")*1="08:00"*1,1,"")
HTH
Jason
Atlanta, GA
"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!
|