Return value in a certain time frame

[Martinpetersson]

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
	A	B	C	D	E	F	G
1	ID	Good_parts		Time	Week	Year	Date
2	121	30		2022-06-22 06:05	25	2022	2022-06-26
3	216	46		2022-06-22 07:56	25	2022	2022-06-26
4	864	24		2022-06-22 09:55	25	2022	2022-06-26
5	864	30		2022-06-22 11:35	25	2022	2022-06-26
6	121	22		2022-06-22 14:30	25	2022	2022-06-23
7	121	10		2022-06-23 08:30	25	2022	2022-06-23
8	121	5		2022-06-23 10:30	25	2022	2022-06-23
9	130	45		2022-06-23 12:51	25	2022	2022-06-23
10	216	44		2022-06-23 13:18	25	2022	2022-06-23
11	130	103		2022-06-23 13:39	25	2022	2022-06-23
12	120	75		2022-06-23 14:28	25	2022	2022-06-23
13	121	20		2022-06-23 14:30	25	2022	2022-06-23
14	121	10		2022-06-23 17:01	25	2022	2022-06-26
15	121	5		2022-06-23 18:30	25	2022	2022-06-23
16	864	24		2022-06-23 18:55	25	2022	2022-06-26
17	864	30		2022-06-23 18:55	25	2022	2022-06-26
18	216	46		2022-06-23 18:56	25	2022	2022-06-26
19	121	10		2022-06-23 19:01	25	2022	2022-06-26
20	121	30		2022-06-23 22:30	25	2022	2022-06-23
21
22		Sheet 2 below
23
24		Date to check up		Good parts with ID 121 between 06.00 -14.59
25		2022-06-23		Should be 55
26		Write any date ex. 2022-06-23		Return sum of good parts in that timeframe
27
28		Time and date now		Good parts with ID 121 between 06.00 -00:15
29		2022-06-27 14:51		Should be 55
30		Actual date now		Return sum of good parts in that timeframe
test

Cell Formulas
Range	Formula
D25	D25	=+D29
B29	B29	=NOW()

Thank you for any help!

 

[Martinpetersson]

Here are the final exemple. Hope its easier to understand now. I'm sorry.
please fill in D33 D37 D42 D46

S-flow_hus60_utg4.xlsx
	A	B	C	D
1	ID	Good_parts		Time
2	121	30		2022-06-22 06:05
3	216	46		2022-06-22 07:56
4	864	24		2022-06-22 09:55
5	864	30		2022-06-22 11:35
6	121	22		2022-06-22 14:30
7	121	10		2022-06-23 08:30
8	121	5		2022-06-23 10:30
9	130	45		2022-06-23 12:51
10	216	44		2022-06-23 13:18
11	130	103		2022-06-23 13:39
12	120	75		2022-06-23 14:28
13	121	20		2022-06-23 14:30
14	121	10		2022-06-23 17:01
15	121	5		2022-06-23 18:30
16	864	24		2022-06-23 18:55
17	864	30		2022-06-23 18:55
18	216	46		2022-06-23 18:56
19	121	10		2022-06-23 19:01
20	121	30		2022-06-23 22:30
21	121	30		2022-06-24 00:12
22	121	30		2022-06-24 00:18
23	121	10		2022-06-27 10:00
24	121	5		2022-06-27 15:30
25
26
27
28
29
30		Sheet 2 below
31
32		Date to check up		Good parts with ID 121 between 06.00 -14.59
33		2022-06-23		35
34		Write any date ex. 2022-06-23		Return sum of good parts in that timeframe
35
36				Good parts with ID 121 between 15.00 -00:15
37				85
38				Return sum of good parts in that timeframe
39
40
41		Here i'm looking for the good parts deliverd until a certain time. Now()		Good parts with ID 121 between 06.00 -14.59
42				10
43				Return sum of good parts in that timeframe
44
45		Time and date now		Good parts with ID 121 between 15.00 -00:15
46		2022-06-27 16:05		5
47		Actual date now		Return sum of good parts in that timeframe
test

Cell Formulas
Range	Formula
B46	B46	=NOW()

 

[Martinpetersson]

Have you tried the two formulas I posted?

Yeah, with no success so far. Probably depends on me doing something wrong, will try again. Thank you

 

[etaf]

Ok,
so you say times are

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.

Then in the text you put

Here i'm looking for the good parts deliverd until a certain time. Now()

if you need
6 am to 3 pm from that day.
AND
3 pm to 12 pm (plus 15 minutes on following day.

Fluff did post those formula - which should work - so we need to work out why they are NOT working

BUT if you mean

Here i'm looking for the good parts deliverd until a certain time. Now()

then explain is that the time NOW() up to 00:15

OR
6:00 to NOW()
OR
something else

Both timnes and now

Anyway - here is your later XL2BB with FLUFF formula added

Book1
	A	B	C	D	E
1	ID	Good_parts		Time
2	121	30		6/22/22 6:05
3	216	46		6/22/22 7:56
4	864	24		6/22/22 9:55
5	864	30		6/22/22 11:35
6	121	22		6/22/22 14:30
7	121	10		6/23/22 8:30
8	121	5		6/23/22 10:30
9	130	45		6/23/22 12:51
10	216	44		6/23/22 13:18
11	130	103		6/23/22 13:39
12	120	75		6/23/22 14:28
13	121	20		6/23/22 14:30
14	121	10		6/23/22 17:01
15	121	5		6/23/22 18:30
16	864	24		6/23/22 18:55
17	864	30		6/23/22 18:55
18	216	46		6/23/22 18:56
19	121	10		6/23/22 19:01
20	121	30		6/23/22 22:30
21	121	30		6/24/22 0:12
22	121	30		6/24/22 0:18
23	121	10		6/27/22 10:00
24	121	5		6/27/22 15:30
25
26
27
28
29
30		Sheet 2 below
31
32		Date to check up		Good parts with ID 121 between 06.00 -14.59
33		6/23/22 0:00		35	35
34		Write any date ex. 2022-06-23		Return sum of good parts in that timeframe
35
36				Good parts with ID 121 between 15.00 -00:15
37				85	85
38				Return sum of good parts in that timeframe
39
40
41		Here i'm looking for the good parts deliverd until a certain time. Now()		Good parts with ID 121 between 06.00 -14.59
42				10
43				Return sum of good parts in that timeframe
44
45		Time and date now		Good parts with ID 121 between 15.00 -00:15
46		6/27/22 15:20		5
47		Actual date now		Return sum of good parts in that timeframe
48
Sheet2

Cell Formulas
Range	Formula
E33	E33	=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&B33+TIME(6,0,0),D2:D24,"<="&B33+TIME(14,59,0))
E37	E37	=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&INT(B33)+TIME(15,0,0),D2:D24,"<="&INT(B33)+1+TIME(0,15,0))
B46	B46	=NOW()

 

[Fluff]

As your data is on different sheets, try it like

Fluff.xlsm
	A	B	C	D
1	ID	Good_parts		Time
2	121	30		22/06/2022 06:05
3	216	46		22/06/2022 07:56
4	864	24		22/06/2022 09:55
5	864	30		22/06/2022 11:35
6	121	22		22/06/2022 14:30
7	121	10		23/06/2022 08:30
8	121	5		23/06/2022 10:30
9	130	45		23/06/2022 12:51
10	216	44		23/06/2022 13:18
11	130	103		23/06/2022 13:39
12	120	75		23/06/2022 14:28
13	121	20		23/06/2022 14:30
14	121	10		23/06/2022 17:01
15	121	5		23/06/2022 18:30
16	864	24		23/06/2022 18:55
17	864	30		23/06/2022 18:55
18	216	46		23/06/2022 18:56
19	121	10		23/06/2022 19:01
20	121	30		23/06/2022 22:30
21	121	30		24/06/2022 00:12
22	121	30		24/06/2022 00:18
23	121	10		27/06/2022 10:00
24	121	5		27/06/2022 15:30
25
Data

Fluff.xlsm
	A	B	C	D
1		Date to check up		Good parts with ID 121 between 06.00 -14.59
2		23/06/2022		35
3		Write any date ex. 2022-06-23		Return sum of good parts in that timeframe
4
5				Good parts with ID 121 between 15.00 -00:15
6				85
7				Return sum of good parts in that timeframe
8
9
10		Here i'm looking for the good parts deliverd until a certain time. Now()		Good parts with ID 121 between 06.00 -14.59
11				10
12				Return sum of good parts in that timeframe
13
14		Time and date now		Good parts with ID 121 between 15.00 -00:15
15		27/06/2022 15:14		5
16		Actual date now		Return sum of good parts in that timeframe
Main

Cell Formulas
Range	Formula
D2	D2	=SUMIFS(Data!B:B,Data!A:A,121,Data!D:D,">="&B2+TIME(6,0,0),Data!D:D,"<="&B2+TIME(14,59,0))
D6	D6	=SUMIFS(Data!B:B,Data!A:A,121,Data!D:D,">="&B2+TIME(15,0,0),Data!D:D,"<="&B2+1+TIME(0,15,0))
B15	B15	=NOW()

But like etaf I don't understand the 2nd part

 

[etaf]

to use NOW , you need to change the data to use todays date

Do you want them to use a date in a cell - BUT take the time as off NOW() ?????
OR will it be using todays date anyway

I only ask - because NOW() returns todays date and time now
so if you use just
NOW()
that would search for everything say greater than NOW
BUT if you wanted them to put a date into a cell , but add the TIME NOW to it - thats a different formula
to get JUST the time now without a date - would be NOW() - TRUNC(now())
so you could use
b33 with the date of the 23rd in but add the time NOW

I dont se ehow that will help with real time and would think you just need NOW()

so 06:00 upto the time NOW
=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&TODAY()+TIME(6,0,0),D2:D24,"<="&NOW())
and
NOW upto 00:15 next day
=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&NOW(),D2:D24,"<="&INT(TODAY())+1+TIME(0,15,0))
although that will produce errors if used before 06:00 and after midnight

BUT just wondered is that what you mean -

otherwise CLEAR description of what you need

dates changed to today - so you may need to work out the numbers when you copy into the sheet as its using NOW()

Book1
	A	B	C	D	E
1	ID	Good_parts		Time
2	121	30		6/22/22 6:05
3	216	46		6/22/22 7:56
4	864	24		6/22/22 9:55
5	864	30		6/22/22 11:35
6	121	22		6/22/22 14:30
7	121	10		6/27/22 8:30	7/1/22 8:30
8	121	5		6/27/22 10:30	7/1/22 10:30
9	130	45		6/27/22 12:51	7/1/22 12:51
10	216	44		6/27/22 13:18	7/1/22 13:18
11	130	103		6/27/22 13:39	7/1/22 13:39
12	120	75		6/27/22 14:28	7/1/22 14:28
13	121	20		6/27/22 14:30	7/1/22 14:30
14	121	10		6/27/22 17:01	7/1/22 17:01
15	121	5		6/27/22 18:30	7/1/22 18:30
16	864	24		6/27/22 18:55	7/1/22 18:55
17	864	30		6/27/22 18:55	7/1/22 18:55
18	216	46		6/27/22 18:56	7/1/22 18:56
19	121	10		6/27/22 19:01	7/1/22 19:01
20	121	30		6/27/22 22:30	7/1/22 22:30
21	121	30		6/28/22 0:12
22	121	30		6/28/22 0:18
23	121	10		6/27/22 10:00
24	121	5		6/27/22 15:30
25
26
27
28
29
30		Sheet 2 below
31
32		Date to check up		Good parts with ID 121 between 06.00 -14.59
33		6/27/22 0:00		35	45
34		Write any date ex. 2022-06-23		Return sum of good parts in that timeframe
35
36				Good parts with ID 121 between 15.00 -00:15
37				85	90
38				Return sum of good parts in that timeframe
39
40
41		Here i'm looking for the good parts deliverd until a certain time. Now()		Good parts with ID 121 between 06.00 -14.59
42				10	50
43				Return sum of good parts in that timeframe
44
45		Time and date now		Good parts with ID 121 between 15.00 -00:15
46		6/27/22 16:12		5	85
47		Actual date now		Return sum of good parts in that timeframe
48
Sheet2

Cell Formulas
Range	Formula
E7:E20	E7	=D7+4
E33	E33	=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&B33+TIME(6,0,0),D2:D24,"<="&B33+TIME(14,59,0))
E37	E37	=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&INT(B33)+TIME(15,0,0),D2:D24,"<="&INT(B33)+1+TIME(0,15,0))
E42	E42	=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&TODAY()+TIME(6,0,0),D2:D24,"<="&NOW())
B46	B46	=NOW()
E46	E46	=SUMIFS(B2:B24,A2:A24,121,D2:D24,">="&NOW(),D2:D24,"<="&INT(TODAY())+1+TIME(0,15,0))

 

[Martinpetersson]

Sometimes I want to search for a date and see the results for that day divided by the two different time frames. That Works now! Thank you so much Fluff and etaf!
Fluff formula did the work!

secondly I wanted to see the sum for the current day and of course that works fine too. Then I just use the same formulas... Thank you for all the help!

 

[etaf]

you are welcome
glad its all resolved now