Dates every 15 min show a Reference number

[shonaart]

Hi Guys

I have a Column A with dates & times eg: ( 2019/08/11 06:00 ) running, for example, continues for a number of days.
In Column B I would like to show a numerical number starting from 1 .... every 15min. So example from 19:12 to 19:27 column B with show a 1 then from 19:28 to 19:43 will show a 2 and so on every 15min etc

Column A
2019/08/04 Sun 19:12
2019/08/04 Sun 19:13
2019/08/04 Sun 19:18
2019/08/04 Sun 19:19
2019/08/04 Sun 19:20
2019/08/04 Sun 19:26
2019/08/04 Sun 19:27
2019/08/04 Sun 19:28
2019/08/04 Sun 19:33
2019/08/04 Sun 19:34
2019/08/04 Sun 19:38
2019/08/04 Sun 19:38
2019/08/04 Sun 19:39
2019/08/04 Sun 19:40
2019/08/04 Sun 19:42
2019/08/04 Sun 19:45
2019/08/04 Sun 19:45
2019/08/04 Sun 19:46
2019/08/04 Sun 19:46
2019/08/04 Sun 19:47

Maybe there another way of looking at this. What I am trying to achieve is to count how many dates & times ( Time stamp ) happens every 15 Minutes. So from 19:12 to 19:27 there were 7 timetamps and the second 15min it was 8 x timestamps. The Date& time is everytime a container was moved by a crane.


Thanks I would appreciate your help.

 

[Norie]

Try this in B1 and copied down.

=SUMPRODUCT(--(FLOOR($A$1:A1,"0:15")=FLOOR(A1,"0:15")))

 

[tyija1995]

Hey

Is it something like this that you're looking for?

	A	B
1	2019/08/04 Sun 19:12	1
2	2019/08/04 Sun 19:13	1
3	2019/08/04 Sun 19:18	1
4	2019/08/04 Sun 19:19	1
5	2019/08/04 Sun 19:20	1
6	2019/08/04 Sun 19:26	1
7	2019/08/04 Sun 19:27	1
8	2019/08/04 Sun 19:28	2
9	2019/08/04 Sun 19:33	2
10	2019/08/04 Sun 19:34	2
11	2019/08/04 Sun 19:38	2
12	2019/08/04 Sun 19:39	2
13	2019/08/04 Sun 19:40	2
14	2019/08/04 Sun 19:42	2
15	2019/08/04 Sun 19:45	3
16	2019/08/04 Sun 19:45	3
17	2019/08/04 Sun 19:46	3
18	2019/08/04 Sun 19:46	3
19	2019/08/04 Sun 19:47	3

<tbody>
</tbody>

Where cell B1 is:

1+ROUNDDOWN((EDATE(LEFT(A1,10),0)+TIME(LEFT(MID(A1,16,99),2),MID(MID(A1,16,99),4,2),0)-43681.8)/(15/60/24),0)

And then dragged down.

The main assumption here is that the generated numbers start at 2019/08/04 19:12 (hence the -43681.8)

You can change this scalar value if needed - perhaps make a cell "StartTime" and input the starting date/time there to use as the reference.

 

[shonaart]

Hi
Thanks for the help, however, I am getting a #Value ! error and I am using it on another table with different dates.
Starting with 2019/08/08 Thu 23:33
Not sure I understand the reference you talking about ( -43681.8)

Thanks again

 

[shonaart]

Thanks Norie,
I added the formula but I am getting numbers 1,2,3 etc not showing the 15min intervals as like the table below.
Thanks again

 

[tyija1995]

Hey,

Are your dates numerical or text?

Try:
=ISNUMBER(cell ref)
=ISTEXT(cell ref)

Which returns TRUE and which returns FALSE ?

 

[shonaart]

Hi
=ISNumber returns a true

Thanks

 

[shonaart]

Hi

This is the formula I added
=1+ROUNDDOWN(EDATE(LEFT(D2;10)+TIME(LEFT(MID(D2;16;99);2);MID(MID(D2;16;99);4;2);0)-43681;8)/(15/60/24);0)

I left out the ,0) out the formula, if I added it, the formula says it's incorrect

 

[tyija1995]

OK so that it is numerical try:

B2:
=1+ROUNDDOWN((A2-startDate)/(15/24/60),0)

Where startDate is your reference point (a named range containing the datetime value) and A2 is the first datetime entry

It should look like this:

	A	B
1	08/08/2019 23:33
2	08/08/2019 23:49	2.00

<tbody>
</tbody>

Using the formula:

	A	B
1	startDate
2	08/08/2019 23:49	=1+ROUNDDOWN((A2-startDate)/(15/24/60),0)

<tbody>
</tbody>

 

[Norie]

shonaart

Can you post the expected result based on the data in your initial post?