IF condition for separate data with in date time range

[Satish.digit]

Hi,


I have one swipe timing report with me for n8 shift employees below is the data for your reference.

[pre]

A       B               C               D
shift	Date	        In Time	        Out Time
B	06/11/2012	03:06:00	05:12:00
B	06/11/2012	05:13:00        05:14:00
B	06/11/2012	19:01:00	23:50:00
B	06/11/2012	00:13:00	03:01:00
B	06/11/2012	05:10:00	07:42:00
B	06/12/2012	19:20:00	20:24:00
B	06/12/2012	01:44:00	03:44:00
B	06/12/2012	04:00:00	04:17:00
B	06/12/2012	18:58:00	19:14:00
B	06/12/2012	20:20:00	23:52:00
B	06/12/2012	00:16:00	01:38:00
B	06/12/2012	04:12:00	07:49:00

[/pre]
in the above report i need only 12th after 19:00hrs to 13th before 06:00hrs.

can any one help on this with the "if" condition.


Finally purpose of the report is i need to calculate eash person how many hours spent in office and this report is one person's only.

 

[Luke M]

Can you clarify what this statement means?

in the above report i need only 12th after 19:00hrs to 13th before 06:00hrs.

I'm afraid I don't understand.

 

[Satish.digit]

Oh sorry it should be 11th after 19:00hrs to 12th before 06:00hrs.

 

[Luke M]

What do you want us to do with those rows? Are we simply flagging them for later?

=IF(OR(AND(DAY(B2)=11,C2>19/24,D2>19/24),AND(DAY(B2)=12,C2<6/24)),"Meets criteria","Ignore")

 

[Satish.digit]

Thanks Luke,


Your the master in Excel. thanks a ton for your help on this.


I am going to remove those rows manually if you have any better idea please suggest.


Regards,

Satish.

 

[Luke M]

Depending on what you're going to do, that sounds okay. I might recommend just filtering as I'm usually hesitant to "delete" stuff.