FORMULA REQUIRED

[shaji]

I am getting dailly telecast data in the following format. I have to compare
that the programme had aired between the scheduled timings. In the first
entry I have to check the telecast time is between 10:00:00 and 10:27:00

Date Schedl Time Telecast Time
06/09/2006 1000-1027 10:13:01
06/09/2006 1100-1147 11:11:40
06/09/2006 1100-1147 11:36:50
06/09/2006 1200-1247 12:27:31
06/09/2006 1200-1247 12:37:47
06/09/2006 1500-1527 15:22:56
06/09/2006 1600-1627 16:16:35
06/09/2006 1630-1657 16:54:04
06/09/2006 1700-1727 17:18:24
06/09/2006 1800-1857 18:20:47
06/09/2006 1800-1857 18:35:33
06/09/2006 1800-1857 18:48:16
06/09/2006 2300-2400 23:19:47
06/09/2006 2300-2400 23:33:56
06/09/2006 2300-2400 23:39:02

can any one help me with a formula or a vb code for the same.

thanks in advance.

 

[Dave O]

I copied your sample data including headers into A1:C16 and saw that
Excel interpreted the telecast time as a serial date (the way Excel
keeps track of dates and times). So I converted the time into a 4
digit number (disregarding seconds) so it would compare to the 4 digit
Schedl Time figures. I got the expected results with this formula in
D2:
=IF(AND(VALUE(HOUR(C2)&MINUTE(C2))>=VALUE(LEFT(B2,4)),VALUE(HOUR(C2)&MINUTE(C2))<=VALUE(RIGHT(B2,4))),"Within
timeframe","Outside timeframe")

You can copy and paste that formula into remaining cells.

 

[Ron Coderre]

Try something like this:

With your sample data in A1:C16

This formula returns TRUE if the time in C2 is within the range in B2:
D2:
=AND(C2>=(--(LEFT(B2,2)&":"&MID(B2,3,2))),C2<=(--(MID(B2,6,2)&":"&RIGHT(B2,2))))

Does that help?
***********
Regards,
Ron

XL2002, WinXP