Adding a new time causes wrong average

[JenniferMurphy]

This is the first 2 columns of table. The cell in question is the average in C59 with the value "10:24a". In the second mini-sheet below, I added one row with a time of 11:51a and the average, now in C60, became "1:34a". What have I done wrong now?

Blood glucose readings.xlsx
	B	C
5	Date	Time
6	Mon 01/01/24	2:51p
7	Tue 01/02/24	8:47a
8	Wed 01/03/24	10:25a
9	Thu 01/04/24	8:43a
10	Fri 01/05/24	8:15a
11	Sat 01/06/24	10:32a
12	Sun 01/07/24	9:46a
13	Mon 01/08/24	9:29a
14	Tue 01/09/24
15	Wed 01/10/24	4:24p
16	Thu 01/11/24	7:54a
17	Fri 01/12/24	8:01a
18	Sat 01/13/24	8:25a
19	Sun 01/14/24	9:10a
20	Mon 01/15/24	2:08p
21	Tue 01/16/24
22	Wed 01/17/24	9:47a
23	Thu 01/18/24	10:17a
24	Fri 01/19/24	8:33a
25	Sat 01/20/24	8:11a
26	Sun 01/21/24
27	Mon 01/22/24	7:52a
28	Tue 01/23/24	2:15p
29	Wed 01/24/24	9:24a
30	Thu 01/25/24
31	Fri 01/26/24	10:03a
32	Sat 01/27/24	9:12a
33	Sun 01/28/24	9:05a
34	Mon 01/29/24	6:15a
35	Tue 01/30/24	12:14p
36	Wed 01/31/24	10:48a
37	Thu 02/01/24	9:06a
38	Fri 02/02/24	10:37a
39	Sat 02/03/24	10:16a
40	Sun 02/04/24
41	Mon 02/05/24	11:22p
42	Tue 02/06/24	6:03p
43	Wed 02/07/24	10:31a
44	Thu 02/08/24
45	Fri 02/09/24	9:21a
46	Sat 02/10/24	9:07a
47	Sun 02/11/24	10:34a
48	Mon 02/12/24	10:25a
49	Tue 02/13/24	7:29a
50	Wed 02/14/24	10:37a
51	Thu 02/15/24	11:01a
52	Fri 02/16/24	8:12a
53	Sat 02/17/24	9:42a
54	Sun 02/18/24	9:50a
55	Mon 02/19/24	8:58a
56	Tue 02/20/24	10:32a
57	51	45
58	Earliest	6:15a
59	Average	10:24a
60	Latest	2:08p
20240101

Cell Formulas
Range	Formula
B7:B56	B7	=OFFSET([@Date],-1,0)+1
B57	B57	=SUBTOTAL(103,[Date])
C57	C57	=SUBTOTAL(102,[Time])
C58	C58	=MIN(Table1[Time])
C59	C59	=AVERAGE(Table1[Time])
C60	C60	=MAX(Table1[Time])

Second table:

Blood glucose readings.xlsx
	B	C
5	Date	Time
6	Mon 01/01/24	2:51p
7	Tue 01/02/24	8:47a
8	Wed 01/03/24	10:25a
9	Thu 01/04/24	8:43a
10	Fri 01/05/24	8:15a
11	Sat 01/06/24	10:32a
12	Sun 01/07/24	9:46a
13	Mon 01/08/24	9:29a
14	Tue 01/09/24
15	Wed 01/10/24	4:24p
16	Thu 01/11/24	7:54a
17	Fri 01/12/24	8:01a
18	Sat 01/13/24	8:25a
19	Sun 01/14/24	9:10a
20	Mon 01/15/24	2:08p
21	Tue 01/16/24
22	Wed 01/17/24	9:47a
23	Thu 01/18/24	10:17a
24	Fri 01/19/24	8:33a
25	Sat 01/20/24	8:11a
26	Sun 01/21/24
27	Mon 01/22/24	7:52a
28	Tue 01/23/24	2:15p
29	Wed 01/24/24	9:24a
30	Thu 01/25/24
31	Fri 01/26/24	10:03a
32	Sat 01/27/24	9:12a
33	Sun 01/28/24	9:05a
34	Mon 01/29/24	6:15a
35	Tue 01/30/24	12:14p
36	Wed 01/31/24	10:48a
37	Thu 02/01/24	9:06a
38	Fri 02/02/24	10:37a
39	Sat 02/03/24	10:16a
40	Sun 02/04/24
41	Mon 02/05/24	11:22p
42	Tue 02/06/24	6:03p
43	Wed 02/07/24	10:31a
44	Thu 02/08/24
45	Fri 02/09/24	9:21a
46	Sat 02/10/24	9:07a
47	Sun 02/11/24	10:34a
48	Mon 02/12/24	10:25a
49	Tue 02/13/24	7:29a
50	Wed 02/14/24	10:37a
51	Thu 02/15/24	11:01a
52	Fri 02/16/24	8:12a
53	Sat 02/17/24	9:42a
54	Sun 02/18/24	9:50a
55	Mon 02/19/24	8:58a
56	Tue 02/20/24	10:32a
57	Wed 02/21/24	11:51a
58	52	46
59	Earliest	6:15a
60	Average	1:34a
61	Latest	2:08p
20240101

Cell Formulas
Range	Formula
B7:B57	B7	=OFFSET([@Date],-1,0)+1
B58	B58	=SUBTOTAL(103,[Date])
C58	C58	=SUBTOTAL(102,[Time])
C59	C59	=MIN(Table1[Time])
C60	C60	=AVERAGE(Table1[Time])
C61	C61	=MAX(Table1[Time])

 

[awoohaw]

I think your time values also have dates in them and some do not.

Book2
	A	B	C	D	E	F	G	H	I
1
2
3
4
5	Date	Time			Date	Time
6	45292	45292.61875			45292	45292.61875		2024-01-01	2024-01-01 14:51:00
7	=OFFSET([@Date],-1,0)+1	45293.36597			45293	45293.36597		2024-01-02	2024-01-02 08:47:00
8	=OFFSET([@Date],-1,0)+1	45294.43403			45294	45294.43403		2024-01-03	2024-01-03 10:25:00
9	=OFFSET([@Date],-1,0)+1	45295.36319			45295	45295.36319		2024-01-04	2024-01-04 08:43:00
10	=OFFSET([@Date],-1,0)+1	45296.34375			45296	45296.34375		2024-01-05	2024-01-05 08:15:00
11	=OFFSET([@Date],-1,0)+1	45297.43889			45297	45297.43889		2024-01-06	2024-01-06 10:32:00
12	=OFFSET([@Date],-1,0)+1	45298.40694			45298	45298.40694		2024-01-07	2024-01-07 09:46:00
13	=OFFSET([@Date],-1,0)+1	45299.39514			45299	45299.39514		2024-01-08	2024-01-08 09:29:00
14	=OFFSET([@Date],-1,0)+1				45300			2024-01-09	1900-01-00 00:00:00
15	=OFFSET([@Date],-1,0)+1	45301.68333			45301	45301.68333		2024-01-10	2024-01-10 16:24:00
16	=OFFSET([@Date],-1,0)+1	45302.32917			45302	45302.32917		2024-01-11	2024-01-11 07:54:00
17	=OFFSET([@Date],-1,0)+1	45303.33403			45303	45303.33403		2024-01-12	2024-01-12 08:01:00
18	=OFFSET([@Date],-1,0)+1	45304.35069			45304	45304.35069		2024-01-13	2024-01-13 08:25:00
19	=OFFSET([@Date],-1,0)+1	45305.38194			45305	45305.38194		2024-01-14	2024-01-14 09:10:00
20	=OFFSET([@Date],-1,0)+1	45306.58889			45306	45306.58889		2024-01-15	2024-01-15 14:08:00
21	=OFFSET([@Date],-1,0)+1				45307			2024-01-16	1900-01-00 00:00:00
22	=OFFSET([@Date],-1,0)+1	0.407638889			45308	0.407638889		2024-01-17	1900-01-00 09:47:00
23	=OFFSET([@Date],-1,0)+1	0.428472222			45309	0.428472222		2024-01-18	1900-01-00 10:17:00
24	=OFFSET([@Date],-1,0)+1	0.35625			45310	0.35625		2024-01-19	1900-01-00 08:33:00
25	=OFFSET([@Date],-1,0)+1	0.340972222			45311	0.340972222		2024-01-20	1900-01-00 08:11:00
26	=OFFSET([@Date],-1,0)+1				45312			2024-01-21	1900-01-00 00:00:00
27	=OFFSET([@Date],-1,0)+1	0.327777778			45313	0.327777778		2024-01-22	1900-01-00 07:52:00
28	=OFFSET([@Date],-1,0)+1	0.59375			45314	0.59375		2024-01-23	1900-01-00 14:15:00
29	=OFFSET([@Date],-1,0)+1	0.391666667			45315	0.391666667		2024-01-24	1900-01-00 09:24:00
30	=OFFSET([@Date],-1,0)+1				45316			2024-01-25	1900-01-00 00:00:00
31	=OFFSET([@Date],-1,0)+1	0.41875			45317	0.41875		2024-01-26	1900-01-00 10:03:00
32	=OFFSET([@Date],-1,0)+1	0.383333333			45318	0.383333333		2024-01-27	1900-01-00 09:12:00
33	=OFFSET([@Date],-1,0)+1	0.378472222			45319	0.378472222		2024-01-28	1900-01-00 09:05:00
34	=OFFSET([@Date],-1,0)+1	0.260416667			45320	0.260416667		2024-01-29	1900-01-00 06:15:00
35	=OFFSET([@Date],-1,0)+1	0.509722222			45321	0.509722222		2024-01-30	1900-01-00 12:14:00
36	=OFFSET([@Date],-1,0)+1	0.45			45322	0.45		2024-01-31	1900-01-00 10:48:00
37	=OFFSET([@Date],-1,0)+1	0.379166667			45323	0.379166667		2024-02-01	1900-01-00 09:06:00
38	=OFFSET([@Date],-1,0)+1	0.442361111			45324	0.442361111		2024-02-02	1900-01-00 10:37:00
39	=OFFSET([@Date],-1,0)+1	0.427777778			45325	0.427777778		2024-02-03	1900-01-00 10:16:00
40	=OFFSET([@Date],-1,0)+1				45326			2024-02-04	1900-01-00 00:00:00
41	=OFFSET([@Date],-1,0)+1	0.973611111			45327	0.973611111		2024-02-05	1900-01-00 23:22:00
42	=OFFSET([@Date],-1,0)+1	0.752083333			45328	0.752083333		2024-02-06	1900-01-00 18:03:00
43	=OFFSET([@Date],-1,0)+1	0.438194444			45329	0.438194444		2024-02-07	1900-01-00 10:31:00
44	=OFFSET([@Date],-1,0)+1				45330			2024-02-08	1900-01-00 00:00:00
45	=OFFSET([@Date],-1,0)+1	0.389583333			45331	0.389583333		2024-02-09	1900-01-00 09:21:00
46	=OFFSET([@Date],-1,0)+1	0.379861111			45332	0.379861111		2024-02-10	1900-01-00 09:07:00
47	=OFFSET([@Date],-1,0)+1	0.440277778			45333	0.440277778		2024-02-11	1900-01-00 10:34:00
48	=OFFSET([@Date],-1,0)+1	0.434027778			45334	0.434027778		2024-02-12	1900-01-00 10:25:00
49	=OFFSET([@Date],-1,0)+1	0.311805556			45335	0.311805556		2024-02-13	1900-01-00 07:29:00
50	=OFFSET([@Date],-1,0)+1	0.442361111			45336	0.442361111		2024-02-14	1900-01-00 10:37:00
51	=OFFSET([@Date],-1,0)+1	0.459027778			45337	0.459027778		2024-02-15	1900-01-00 11:01:00
52	=OFFSET([@Date],-1,0)+1	0.341666667			45338	0.341666667		2024-02-16	1900-01-00 08:12:00
53	=OFFSET([@Date],-1,0)+1	0.404166667			45339	0.404166667		2024-02-17	1900-01-00 09:42:00
54	=OFFSET([@Date],-1,0)+1	0.409722222			45340	0.409722222		2024-02-18	1900-01-00 09:50:00
55	=OFFSET([@Date],-1,0)+1	0.373611111			45341	0.373611111		2024-02-19	1900-01-00 08:58:00
56	=OFFSET([@Date],-1,0)+1	0.438888889			45342	0.438888889		2024-02-20	1900-01-00 10:32:00
57	=SUBTOTAL(103,[Date])	=SUBTOTAL(102,[Time])			Total	14093.43378
58	Earliest	0.260416667
59	Average	14093.43378
60	Latest	45306.58889
Sheet1

Cell Formulas
Range	Formula
H6:H56	H6	=TEXT(Table1[@Date],"yyyy-mm-dd")
I6:I56	I6	=TEXT(Table1[@Time],"yyyy-mm-dd hh:mm:ss")
E7:E56	E7	=OFFSET([@Date],-1,0)+1
F57	F57	=SUBTOTAL(1,[Time])
B58	B58	=MIN(Table1[Time])
B59	B59	=AVERAGE(Table1[Time])
B60	B60	=MAX(Table1[Time])

 

[JenniferMurphy]

I think your time values also have dates in them and some do not.

Yes, thank you. My mistake.

 

[awoohaw]

You're welcome. Happy to help. Everyone makes them once in a while.

Best Wishes!