#1   Report Post  
Old September 12th 06, 03:31 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 28
Default 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   Report Post  
Old September 12th 06, 04:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 427
Default 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   Report Post  
Old September 12th 06, 04:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,118
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM
Help required for formula recklaw Excel Discussion (Misc queries) 2 March 15th 06 11:11 AM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 05:30 AM
addition to my date formula...required Juco Excel Worksheet Functions 5 January 30th 05 11:48 AM
Rate of return required formula Alorasdad Excel Worksheet Functions 1 November 18th 04 03:14 AM


All times are GMT +1. The time now is 07:46 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017