Excel formula if conition formula for 2 condition with consider timings

Hi dude

i need urgent help of excel function formula if condition result hit or miss

i have enclosed excel sheet

"1 st condition is if start time less than 10:00:00 am end time is another day before 23:00:00.
If its true result = ""hit project"", other wise ""miss""
should consider saturday ,should not consider sunday.(mon-sat)"

"2 nd condition is if start time greater than 10:00:00 am end time is another 2 day before 23:00:00 if its true result = ""hit project"", other wise ""miss""
should consider saturday ,should not consider sunday.(mon-sat)"

hi dood,
U can use sumproduct function and if condition i think result will come as for ur requirment.......

Till 9:59 is first condition 10:00 is consider second condition

=IF(AND(A2="Tier 1",MOD(B2,1)<TIMEVALUE("10:00:00"),MOD(C2,1)<TIMEVALUE("23:00:59")),"HIT",IF(AND(MOD(B2,1)>TIMEVALUE("10:00:00"),MOD(C2,1)<TIMEVALUE("23:00:59")),"HIT",IF(AND(MOD(B2,1)<TIMEVALUE("10:00:00"),MOD(C2,1)<TIMEVALUE("23:00:59")),"HIT","MISS")))

i try this formula check if any thing change to get acurate answer

=if(and(hour(b3)<10,networkdays.intl(b3,c3,11)-1<=1,hour(c3)<23),"hit",if(and(hour(b3)>=10,networkdays.intl(b3,c3,11)-1=2,hour(c3)<23),"hit","miss"))

#name? Error occured

"here simple example
if start time before 10:am (15/05/2013) end time can be till same day (15/05/2013)11:00 pm night=hit if its cross 11:00 pm miss
if start time after 10:am (15/05/2013) end time can be till next day (16/05/2013) 11:00 pm night =hit if its cross 11:00 pm miss"