Calculate number of sessions per time period

[MattFree]

Hello all,

I am hoping someone can help me, I am struggling to find a way of auto calculating this.

I need to show the expected session against the time given to an individual.
Individuals are expected to carry out 1 session per 45min period

Time Given (hours)	Number of Expected Sessions
00:45	1
01:30	2

Hope this makes sense.

 

[Georgiboy]

If they are time values then maybe the below formula:

=ROUNDDOWN(A2/"00:45:00",0)

 

[Legacy 143009]

Like this?

=FLOOR(A2/0.03125,1)

 

[MattFree]

Thanks for this however its not what I'm looking for.

I need the expected hours to show how many sessions are expected based on the hours given, with a criteria that a 45 minuets period is 1 session.

 

[Legacy 143009]

Then A2:

=TEXT(B2*0.03125, "[h]:mm:ss")

 

[jdellasala]

I think @Flashbond 's first solution works fine:

Book1
	A	B
1	Hours	Sessions
2	0:15	0
3	0:30	0
4	0:45	1
5	1:00	1
6	1:15	1
7	1:30	2
8	1:45	2
9	2:00	2
10	2:15	3
11	2:30	3
12	2:45	3
13	3:00	4
14	3:15	4
15	3:30	4
16	3:45	5
17	4:00	5
18	4:15	5
19	4:30	6
20	4:45	6
21	5:00	6
22	5:15	7
23	5:30	7
24	5:45	7
25	6:00	8
26	6:15	8
27	6:30	8
28	6:45	9
29	7:00	9
30	7:15	9
31	7:30	10
32	7:45	10
33	8:00	10
34	8:15	11
35	8:30	11
36	8:45	11
37	9:00	12
38	9:15	12
39	9:30	12
40	9:45	13
41	10:00	13
42	10:15	13
43	10:30	14
44	10:45	14
45	11:00	14
46	11:15	15
47	11:30	15
48	11:45	15
49	12:00	16
50	12:15	16
51	12:30	16
52	12:45	17
53	13:00	17
54	13:15	17
55	13:30	18
56	13:45	18
57	14:00	18
58	14:15	19
59	14:30	19
60	14:45	19
61	15:00	20
62	15:15	20
63	15:30	20
64	15:45	21
65	16:00	21
66	16:15	21
67	16:30	22
68	16:45	22
69	17:00	22
70	17:15	23
71	17:30	23
72	17:45	23
73	18:00	24
74	18:15	24
75	18:30	24
76	18:45	25
77	19:00	25
78	19:15	25
79	19:30	26
80	19:45	26
81	20:00	26
82	20:15	27
83	20:30	27
84	20:45	27
85	21:00	28
86	21:15	28
87	21:30	28
88	21:45	29
89	22:00	29
90	22:15	29
91	22:30	30
92	22:45	30
93	23:00	30
94	23:15	31
95	23:30	31
96	23:45	31
Sheet2

Cell Formulas
Range	Formula
A2:A96	A2	=SEQUENCE(95,,TIME(0,15,0),TIME(0,15,0))
B2:B96	B2	=ROUNDDOWN(A2#/"00:45:00",0)
Dynamic array formulas.

 

[Legacy 143009]

I think @Flashbond 's first solution works fine:

Book1
	A	B
1	Hours	Sessions
2	0:15	0
3	0:30	0
4	0:45	1
5	1:00	1
6	1:15	1
7	1:30	2
8	1:45	2
9	2:00	2
10	2:15	3
11	2:30	3
12	2:45	3
13	3:00	4
14	3:15	4
15	3:30	4
16	3:45	5
17	4:00	5
18	4:15	5
19	4:30	6
20	4:45	6
21	5:00	6
22	5:15	7
23	5:30	7
24	5:45	7
25	6:00	8
26	6:15	8
27	6:30	8
28	6:45	9
29	7:00	9
30	7:15	9
31	7:30	10
32	7:45	10
33	8:00	10
34	8:15	11
35	8:30	11
36	8:45	11
37	9:00	12
38	9:15	12
39	9:30	12
40	9:45	13
41	10:00	13
42	10:15	13
43	10:30	14
44	10:45	14
45	11:00	14
46	11:15	15
47	11:30	15
48	11:45	15
49	12:00	16
50	12:15	16
51	12:30	16
52	12:45	17
53	13:00	17
54	13:15	17
55	13:30	18
56	13:45	18
57	14:00	18
58	14:15	19
59	14:30	19
60	14:45	19
61	15:00	20
62	15:15	20
63	15:30	20
64	15:45	21
65	16:00	21
66	16:15	21
67	16:30	22
68	16:45	22
69	17:00	22
70	17:15	23
71	17:30	23
72	17:45	23
73	18:00	24
74	18:15	24
75	18:30	24
76	18:45	25
77	19:00	25
78	19:15	25
79	19:30	26
80	19:45	26
81	20:00	26
82	20:15	27
83	20:30	27
84	20:45	27
85	21:00	28
86	21:15	28
87	21:30	28
88	21:45	29
89	22:00	29
90	22:15	29
91	22:30	30
92	22:45	30
93	23:00	30
94	23:15	31
95	23:30	31
96	23:45	31
Sheet2

Cell Formulas
Range	Formula
A2:A96	A2	=SEQUENCE(95,,TIME(0,15,0),TIME(0,15,0))
B2:B96	B2	=ROUNDDOWN(A2#/"00:45:00",0)
Dynamic array formulas.

I think he wants to derive column A from column B

 

[MattFree]

Brilliant! This has worked. Thanks all