Determining if a datetime falls within start and end date times

[migooz]

Gents,

Please i need an advise ASAP i have been using this statement and it cant help

if(and(c1>=a1:a144,c1<=b1:b144),"yes","no"))

and it just works for the first 2 values c1, c2 and doesn't fit for the others.
the case is i have more than one event at the same video and i need to confirm that no event was taken unless it is between start and end.

here are some samples:

Start dtime End Dtime Event Dtime
16/09/2013 22:13:34 16/09/2013 22:14:18 16/09/2013 22:13:38
16/09/2013 22:15:57 16/09/2013 22:24:30 16/09/2013 22:16:02
16/09/2013 22:24:30 16/09/2013 22:33:49 16/09/2013 22:17:32
16/09/2013 22:33:53 16/09/2013 22:35:05 16/09/2013 22:19:02
16/09/2013 22:35:05 16/09/2013 22:39:57 16/09/2013 22:20:02

So as you can see there are more than one event between one start and end dtimes

thanks guys

 

[marious]

Don't forget the $ sign for the ranges otherwise the ranges will move down when you drag down the formula. =IF(AND(C1>=$A$1:$A$144,C1<=$B$1:$B$144),TRUE,FALSE)

 

[migooz]

Don't forget the $ sign for the ranges otherwise the ranges will move down when you drag down the formula. =IF(AND(C1>=$A$1:$A$144,C1<=$B$1:$B$144),TRUE,FALSE)

marious i've tried this one but doesnt work starting from c3 as the cell c3 lies in the 2nd video start and end a2,b2

 

[marious]

marious i've tried this one but doesnt work starting from c3 as the cell c3 lies in the 2nd video start and end a2,b2

Forget my previous post. if I understand correctly in column C you have a series of events and you want to lookup in all the range A2:B144 if the event occurred between the start – end dates. Can you test this formula? Column D return the row number where the event occurs, then column E answer the question "More than one occurrence?" Remember to use CTL+SHIFT+ENTER for the formulas in D and E and then drag it down.
<title>Excel Jeanie HTML</title>


<!-- ######### Start Created Html Code To Copy ########## -->


Sheet3

*	A	B	C	D	E

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 169px;"><col style="width: 189px;"><col style="width: 139px;"><col style="width: 88px;"><col style="width: 105px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #CCFFFF"]Start[/TD]
[TD="bgcolor: #CCFFFF"]End[/TD]
[TD="bgcolor: #CCFFFF"]Event[/TD]
[TD="bgcolor: #CCFFFF"]Occurs in row[/TD]
[TD="bgcolor: #CCFFFF"]More than one occurence?[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]16/09/2013 22:13:34[/TD]
[TD="align: right"]16/09/2013 22:14:18 [/TD]
[TD="align: right"]16/09/2013 22:13:38[/TD]
[TD="bgcolor: #969696, align: right"]2[/TD]
[TD="bgcolor: #969696"]No[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]16/09/2013 22:15:57[/TD]
[TD="align: right"]16/09/2013 22:24:30 [/TD]
[TD="align: right"]16/09/2013 22:16:02[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #969696"]Yes[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]16/09/2013 22:24:30[/TD]
[TD="align: right"]16/09/2013 22:33:49 [/TD]
[TD="align: right"]16/09/2013 22:17:32[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #969696"]Yes[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]16/09/2013 22:33:53[/TD]
[TD="align: right"]16/09/2013 22:35:05 [/TD]
[TD="align: right"]16/09/2013 22:19:02[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #969696"]Yes[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]16/09/2013 22:35:05[/TD]
[TD="align: right"]16/09/2013 22:39:57 [/TD]
[TD="align: right"]16/09/2013 22:20:02[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #969696"]Yes[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 {=SUMPRODUCT(--(C2>=$A$2:$A$144)*--(C2<=$B$2:$B$144)*--($A$2:$A$144<>"")*--($B$2:$B$144<>"")*ROW($A$2:$A$144))} E2 {=IF(SUM(--(D2=$D$2:$D$144))>1,"Yes","No")} E3 {=IF(SUM(--(D3=$D$2:$D$144))>1,"Yes","No")} E4 {=IF(SUM(--(D4=$D$2:$D$144))>1,"Yes","No")} E5 {=IF(SUM(--(D5=$D$2:$D$144))>1,"Yes","No")} E6 {=IF(SUM(--(D6=$D$2:$D$144))>1,"Yes","No")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


　

　

<!-- ######### End Created Html Code To Copy ########## -->