Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA REQUIRED
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA REQUIRED
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA REQUIRED
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 "shaji" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Help required for formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
addition to my date formula...required | Excel Worksheet Functions | |||
Rate of return required formula | Excel Worksheet Functions |