Return value in a certain time frame

M

Martinpetersson

New Member
Joined
Apr 27, 2022
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Look up a value from a table on another sheet, based on its ID number and in a certain time frame. See attached minisheet.

I've made this testsheet, similar to the real one. Please help me to fill in formula in D25 & D29

S-flow_hus60_utg4.xlsx
ABCDEFG
1IDGood_partsTimeWeekYearDate
2121302022-06-22 06:052520222022-06-26
3216462022-06-22 07:562520222022-06-26
4864242022-06-22 09:552520222022-06-26
5864302022-06-22 11:352520222022-06-26
6121222022-06-22 14:302520222022-06-23
7121102022-06-23 08:302520222022-06-23
812152022-06-23 10:302520222022-06-23
9130452022-06-23 12:512520222022-06-23
10216442022-06-23 13:182520222022-06-23
111301032022-06-23 13:392520222022-06-23
12120752022-06-23 14:282520222022-06-23
13121202022-06-23 14:302520222022-06-23
14121102022-06-23 17:012520222022-06-26
1512152022-06-23 18:302520222022-06-23
16864242022-06-23 18:552520222022-06-26
17864302022-06-23 18:552520222022-06-26
18216462022-06-23 18:562520222022-06-26
19121102022-06-23 19:012520222022-06-26
20121302022-06-23 22:302520222022-06-23
21
22Sheet 2 below
23
24Date to check upGood parts with ID 121 between 06.00 -14.59
252022-06-23Should be 55
26Write any date ex. 2022-06-23Return sum of good parts in that timeframe
27
28Time and date nowGood parts with ID 121 between 06.00 -00:15
292022-06-27 14:51Should be 55
30Actual date nowReturn sum of good parts in that timeframe
test
Cell Formulas
RangeFormula
D25D25=+D29
B29B29=NOW()


Thank you for any help!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
ok, a SUMIFS() does work - BUT , i'm not sure how you arrive at the times
so i had to hardcode those for the 35
=SUMIFS(B2:B20,D2:D20,">="&INT(B25)+TIMEVALUE("06:00:00"),D2:D20,"<="&INT(B25)+TIMEVALUE("14:59:00"),A2:A20,"121")
and the ID - which all needs to be more dynamic I guess

So why the times 06:00 to 14:59
then
06:00 to 0:15 - so is that 1/4past midnight - next day 24th ??

OR is that 00:15 to 06:00
I get 90, for 06:00 to 00:00 , no data for next day - so would need to add that

But i need more clarification on a) what the times are - and b) if dynamic what cells are the dates, times, IDs entered
 
Upvote 0
For D25 try
Excel Formula: 
=SUMIFS(B2:B20,A2:A20,121,D2:D20,">="&B25+TIME(6,0,0),D2:D20,"<="&B25+TIME(14,59,0))
 
Upvote 0
etaf said:
So why the times 06:00 to 14:59
then
06:00 to 0:15 - so is that 1/4past midnight - next day 24th ??
Click to expand...
Somthing must have happend when i did the mini sheet. It is suppose to be 06.00 to 14.59 and second 15.00 - 00.15
 
Upvote 0
will it always be those times - hardcoded into the formula, or does it depend on the entries in D25 / D29
and is 00:15 , the next day 24th ?

Not really following

also of course NOW() is 27th
 
Upvote 0
For that you can use
Excel Formula: 
=SUMIFS(B2:B20,A2:A20,121,D2:D20,">="&B25+TIME(15,0,0),D2:D20,"<="&B25+1+TIME(0,15,0))
 
Upvote 0
I will try again. Look at this post - forget the above one.

S-flow_hus60_utg4.xlsx
ABCDEFGH
1IDGood_partsTimeWeekYearDate
2121302022-06-22 06:052520222022-06-26
3216462022-06-22 07:562520222022-06-26
4864242022-06-22 09:552520222022-06-26
5864302022-06-22 11:352520222022-06-26
6121222022-06-22 14:302520222022-06-23
7121102022-06-23 08:302520222022-06-23
812152022-06-23 10:302520222022-06-23
9130452022-06-23 12:512520222022-06-23
10216442022-06-23 13:182520222022-06-23
111301032022-06-23 13:392520222022-06-23
12120752022-06-23 14:282520222022-06-23
13121202022-06-23 14:302520222022-06-23
14121102022-06-23 17:012520222022-06-26
1512152022-06-23 18:302520222022-06-23
16864242022-06-23 18:552520222022-06-26
17864302022-06-23 18:552520222022-06-26
18216462022-06-23 18:562520222022-06-26
19121102022-06-23 19:012520222022-06-26
20121302022-06-23 22:302520222022-06-23
21
22Sheet 2 below
23
24Date to check upGood parts with ID 121 between 06.00 -14.59 Return good parts from picked date
252022-06-23should be 35
26Write any date ex. 2022-06-23Return sum of good parts in that timeframe
27
28Time and date nowGood parts with ID 121 between 06.00 -00:15Return good parts from picked date
292022-06-27 15:32Should be 55
30Actual date nowReturn sum of good parts in that timeframe
31
32
33If now() is 18:45 - then answer should beGood parts with ID 121 between 06.00 -14.59
34should be 35
35Return sum of good parts in that timeframe
36
37Good parts with ID 121 between 06.00 -00:15
38Should be 15
39Return sum of good parts in that timeframe
test
Cell Formulas
RangeFormula
B29B29=NOW()


Hear is a view of the original sheet, Its a live data sheet . adds new rows every minute.


S-flow_hus60_utg4.xlsx
ABCDEFGHIJK
1per_idarb_antal_gdkarb_antal_kassarb_datum_utarb_anstnrkass_kodWeekYeardatumVeckodagdatumminus15min
2885002022-06-27 14:51FV212620222022-06-2722022-06-27 14:36
arbete
Cell Formulas
RangeFormula
G2G2=ISOWEEKNUM([@[arb_datum_ut]])
H2H2=YEAR([@[arb_datum_ut]])
I2I2=TEXT([@datumminus15min],"ÅÅÅÅ-MM-DD")
J2J2=WEEKDAY([@datum])
K2K2=[@[arb_datum_ut]]-0.01



hope it is easier now, thank you!
 
Upvote 0
etaf said:
will it always be those times - hardcoded into the formula, or does it depend on the entries in D25 / D29
and is 00:15 , the next day 24th ?

Not really following

also of course NOW() is 27th
Click to expand...

The "user" will be able to search for a certain date. Then the user will see the good parts delivered during that date. It is important to see the good parts from 6 am to 3 pm from that day. It iks also important to see the good parts from 3 pm to 12 pm (plus 15 minutes on following day.

I understand i made a really ****ty example when there are no dates the 24th.

The list with entries is very long and new rows comes every minute during the day.

Sorry for a really bad exemple and question. Hope it is more clearly now.
 
Upvote 0
Have you tried the two formulas I posted?
 
Upvote 0
You must log in or register to reply here.

Similar threads

AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
285
AOB
AOB
JeffGrant
  • Question
Schedule Macros with Application.OnTime with looped in variable times
Replies
0
Views
224
JeffGrant
JeffGrant
A
  • Solved
Merge data for each range based on two words & dates
2
Replies
10
Views
269
abdo meghari
A
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
398
archibell
A
J
  • Solved
Difference between dates based on follow on work
Replies
5
Views
417
Alex Blakenburg
A

Forum statistics

Threads
1,215,368
Messages
6,124,521
Members
449,169
Latest member
mm424

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top