Dynamic chart based on last 24 hours

[Craig1]

Hi Guys,

I've created a chart regarding production and would like to make it dynamic, but it's not a straight forward dynamic chart, I don't think.
Basically what I need is the chart to find the data for the last 24 hours. In column A I have the batch number which is the Horizontal Axis Labels and in column B & C I have the start time and end time of the batch. The 2 lots of data are in columns F & H.
So for columns A, F & H I want them to look back until the find the batch that was active 24 hours ago.

Hope I have explained myself correctly. In the example below I would want to start from CRTAA80008 and back for 24 hours which in this case would be CRTAA80001 because this was copied at 01:44 so going back 24 hours this batch was active over the 24 hour mark.

Sorry I can't use excel Jeanie, work restrictions.

CRTAA80009 28-Sep-14 01:36:51 * 0:08 7996 4736 CRT 851 1195
CRTAA80008 28-Sep-14 00:48:17 28-Sep-14 01:36:51 0:48 1476 997 CRT 851 1195
CRTAA80007 27-Sep-14 22:30:01 28-Sep-14 00:48:17 2:18 519 1135 CRT 851 1195
CRTAA80006 27-Sep-14 21:49:05 27-Sep-14 22:30:01 0:40 1752 941 CRT 851 1195
CRTAA80005 27-Sep-14 12:38:04 27-Sep-14 21:49:05 9:11 130 681 CRT 851 1195
CRTAA80004 27-Sep-14 11:39:50 27-Sep-14 12:38:03 0:58 1231 692 CRT 851 1195
CRTAA80003 27-Sep-14 03:50:22 27-Sep-14 11:39:50 7:49 153 793 CRT 851 1195
CRTAA80002 27-Sep-14 03:00:19 27-Sep-14 03:50:22 0:50 1433 1020 CRT 851 1195
CRTAA80001 27-Sep-14 01:02:12 27-Sep-14 03:00:19 1:58 607 956 CRT 851 1195
BHLAA80094 27-Sep-14 00:03:30 27-Sep-14 00:45:53 0:58 1304 927 BHL 887 1276
BHLAA80093 26-Sep-14 21:44:19 27-Sep-14 00:03:30 2:19 550 1227 BHL 887 1276
BHLAA80092 26-Sep-14 21:04:05 26-Sep-14 21:44:19 0:40 1903 1219 BHL 887 1276

Thanks in advance.

Craig.

 

[Craig1]

Sorry Guys, put the wrong code up, the amended code is as follows.
{=IF(ROWS($3:3)>$M$1,"",SMALL(IF($C$3:$C$35<>"",IF($B$3:$B$35>$L$1,$B$3:$B$35)),ROWS($3:3)))}

Craig.

 

[cyrilbrd]

Duc

Sorry Guys, put the wrong code up, the amended code is as follows.
{=IF(ROWS($3:3)>$M$1,"",SMALL(IF($C$3:$C$35<>"",IF($B$3:$B$35>$L$1,$B$3:$B$35)),ROWS($3:3)))}

Craig.

Glad to see it worked for you, as mentioned ranges were to be adjusted to fit your query.
Thanks for the feedback.

 

[Craig1]

Hi cyrilbrd, the amended code is one batch out. Sorry for not explaining myself right. It is still finding the first batch that started after the 24 hour period and not the batch that was active 24 hours ago.

Craig.

p.s I seem to have an issue with the chart using the named ranges. Even though it is seeing the correct data it won't transfer to the chart because it is saying I have something wrong, but I can't see what???

 

[Craig1]

Hi Guys,
Eventually sorted the named range, I was trying everything but what was required. I tried the named range only, I tried it with the workbook name and eventually tried it with sheet name and named range and it worked perfect. Only thing left to do is the active batch 24 hours ago

Thanks again

Craig.

 

[Craig1]

Hi Guys,
I have had some great support for this problem so far, but I have one hurdle to get over, the function is finding the first batch to start after the NOW()-1 (24 hours) ago and not the batch that was active. Any chance any of you experts try to get me over the last hurdle. The function works great except for this last little bit, the function is:-
=IF(ROWS($1:1)>$M$1,"",SMALL(IF($C$2:$C$35<>"",IF($B$2:$B$35>$L$1,$B$2:$B$34)),ROWS($1:1)))
At time of data sample is 20:30, so it should have gone to batch 57 because this was the batch that was active at 20:30 24 hours ago, instead the function is picking 58 batch because this was the one that was the first batch after the 24 hours ago.

CRTl880062 03-Oct-14 02:49:01 03-Oct-14 16:23:49
CRTl880061 03-Oct-14 01:07:38 03-Oct-14 02:49:01
CRTl880060 02-Oct-14 23:48:44 03-Oct-14 01:07:38
CRTl880059 02-Oct-14 22:07:03 02-Oct-14 23:48:44
CRTl880058 02-Oct-14 20:50:34 02-Oct-14 22:07:03
CRTl880057 02-Oct-14 19:04:33 02-Oct-14 20:50:34


Any help guys is always appreciated and thanks to Cyrilbrd and Oldbrewer for the help so far.

Craig.

 

[cyrilbrd]

Based on your sample, what is value for now()-1 and what is expected range to return? Thanks...

 

[Craig1]

Hi Cyrilbrd, thanks for the reply.
The value at the sample time was 20:30 on the 3rd October and I was expected it to pull the data down to the 57 batch which at that time was in row 7 instead of the row 6 it delivered up till. Row 6 was the start of the first batch after 20:30 on the 2nd October (starting at 20:50). Row 7 had the batch in that was active at the 20:30 mark on the 2nd October.
Hope I have explained this correctly for you to understand.

Thanks for the time and patience.

Craig.

 

[cyrilbrd]

You wanted crtl880057 to be included? But the time is only 19:04 and the condition stated 20:30, hence not part of the query, or shall we include it because it ends at 20:50? So 57 to 62?

 

[Craig1]

Thanks for the support Cyrilbrd, it is really appreciated.

Your right in thinking that it's the 57 batch to be included, simply because this was the active batch at 20:30, so, even though it started at 19:04 it didn't finish until 20:50, hence this batch was active at 20:30.
In this example you are correct that it would be 57 to 62.

Again, thanks for the support.

Craig.

 

[cyrilbrd]

Then we should use the range C instead of B in the formulae. This is sent from my phone, so bear with typos please...
=countifs(c2:c33,">"&l1,c2:c33,"<>"&"")
Then in CSE change range column b to range column c, and give it a try...