Help with simple fucntion

[abberyfarm]

Hi there,

I have date and time in column A. Would anybody know a function to put numbers in column B that increase by 1 in each 15 minute block?

Here is an example.

Appreciate any help

Excel Workbook
	A	B
1	Date/Time	*
2	01/09/2014 00:00:10	0
3	01/09/2014 00:01:07	0
4	01/09/2014 00:11:43	0
5	01/09/2014 00:12:41	0
6	01/09/2014 00:13:39	0
7	01/09/2014 00:14:36	0
8	01/09/2014 00:15:34	1
9	01/09/2014 00:16:32	1
10	01/09/2014 00:17:30	1
11	01/09/2014 00:18:27	1
12	01/09/2014 00:26:10	1
13	01/09/2014 00:27:07	1
14	01/09/2014 00:28:05	1
15	01/09/2014 00:29:03	1
16	01/09/2014 00:30:01	2
17	01/09/2014 00:30:58	2
18	01/09/2014 00:31:56	2
19	01/09/2014 00:32:54	2
20	01/09/2014 00:43:30	2
21	01/09/2014 00:44:27	2
22	01/09/2014 00:45:25	3
23	01/09/2014 00:46:23	3
24	01/09/2014 00:56:58	3
25	01/09/2014 00:57:56	3
26	01/09/2014 00:58:54	3
27	01/09/2014 00:59:52	3
28	01/09/2014 01:00:49	4
29	01/09/2014 01:05:39	4
30	01/09/2014 01:06:37	4
Sheet1

 

[abberyfarm]

any ideas?

 

[ParamRay]

.
.

=4*hour(a2)+rounddown(minute(a2)/15,0)

 

[abberyfarm]

Hi thanks for your reply.

This formula seems to maxeout at 95 when the date changes and then reset to 0,1,2...etc

Would you know a workaround to keep it increasing even when the date changes?

Many thanks

 

[ParamRay]

Hi thanks for your reply.

This formula seems to maxeout at 95 when the date changes and then reset to 0,1,2...etc

Would you know a workaround to keep it increasing even when the date changes?

Many thanks

Yes, try the following formula instead. Note that I have used 01/09/2014 as the starting point for the count, but you can change this as required:

=24*4*(ROUNDDOWN(A2,0)-DATE(2014,9,1))+(4*HOUR(A2)+ROUNDDOWN(MINUTE(A2)/15,0))