Pull 12 hours Instead of a Whole Day?

[rtroehrig]

So I'm running something like this:

B2 is

=TODAY()

B1 is

=B2-2

C1 is

={(COUNT(Fluids!A:A)+1)-COUNT(IF((INT(INDIRECT("Fluids!A2:A"&MATCH(10^308,Fluids!A:A)))>=INT(B1))*(INT(INDIRECT("Fluids!A2:A"&MATCH(10^308,Fluids!A:A)))<=INT(B2)),INDIRECT("Fluids!O2:O"&MATCH(10^308,Fluids!A:A))))}

And it's great because I can customize B1 to how many days past I want to show. However, one of our departments has too much production to make the purpose useful, because the line items exceed the display. Does anybody have any ideas on how I can make this pull maybe 12 hrs back instead of 1 day? I tried changing B1 to B2-0.5 to no avail. I also tried changing B2 to =NOW() and adjusting the reference column to Date & Time instead of just date, which also didn't work.

 

[jasonb75]

It should work with B2-0.5 if you remove INT from the formula. That said I'm sure that you should be able to use a much simpler formula, maybe

=COUNTIFS(Fluids!A:A,">="&(B1),Fluids!A:A,"<"&B2,Fluids!O:O,"<>1e+100")

Data samples and explanations of what the formula should be doing are always useful, a formula alone is not always obvious to anyone except the person who wrote it. Your formula appears to be subtracting the count that meets the criteria from the total count (i.e. it is counting the rows that don't meet the criteria) but it is possible that I'm misreading it due to the lack of information.

 

[rtroehrig]

It didn't seem like the suggestion for the other formula was working, but removing INT and changing a few things to =NOW() seems to have worked. I was close, but your suggestion finally got me there. Appreciate the help.

I apologize for the lack of information. I figured seeing the formulas and knowing how they interacted would have sufficed and saved me from writing a novel. I'll try to summarize as best I can.

On Sheet 1 we have an indefinite sample sign-in and status update log. On Sheet 2 I am running the above mentioned formulas to pull only so much of this information to screens throughout the plant. This allows us real-time communication on Quality Control samples as the day progresses. This specific department produces more samples than most others, so I was running into the issue of the newer line items extending beyond what would actually fit on the screens in the plant.

What I had missed was the INT and the fact that my module was timestamping just the date in the referenced column. So by removing the INT on Sheet 2 C1, changing =TODAY() to =NOW() on Sheet 2 B2 and adjusting the date stamp formula in the module from = Date to = Now, it appears that the problem has been resolved. However, with it only being Monday with no production over the weekend, I won't be 100% sure until tomorrow or Wednesday.

 

[jasonb75]

saved me from writing a novel. I'll try to summarize as best I can.

I think that you may have misinterpreted what I meant in regards to the information needed (perhaps, ironically due to my lack of information). What is generally needed is a breakdown of what is where, dates in column A, numbers in column O etc along with a brief explanation of how you arrive at the result you expect from the formula.
Going over your original formula again and breaking it down would this summary be correct?

Count the number of dates in column A then subtract the count of numbers in column O where the date / time stamp in column A falls between B1 and B2.

I'm not following the purpose of adding 1 to the first count, this appears to be skewing the result. I'm guessing maybe a company / industry requirement?
This formula appears to return the same results as yours, but this would depend on the contents of column O.

=COUNT(A:A,1)-COUNTIFS(Fluids!A:A,">="&(B1),Fluids!A:A,"<="&B2,Fluids!O:O,"<>1e+100")

I've assumed that column O will have either numeric values or empty cells. If there are formulas that show a blank cell, or any text then the results will be incorrect. This can be fixed by changing <> to < in the last part of the formula, but then it will not work with empty cells.
It is details like this that make the difference between a formula that works and a solution rather than a biography of the business or user.

 

[rtroehrig]

Honestly, its been so long since I designed this tab that I can't really remember why I did it the way I did, but it works lol

The idea was to pull the rows to Sheet 2 based on the date in Sheet 1. I only wanted to show so much of a spread, and it had to pack at the top. I think the +1 was just to skip the headers. The O:O is just referencing the last column in the data field. The three formulas above were designed to create the index. Then in the rest of the table below there is something such as (depending on the column):

=@IF(@INDEX(Fluids!A:A,$C$1+ROWS(A$4:$E4))=0,"",INDEX(Fluids!A:A,$C$1+ROWS(A$4:$E4)))

For this particular column, it is pulling the lot number (A) to Sheet 2 based on the Date (E) in Sheet 1. I guess completing the picture and not leaving that piece out would have helped...