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?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2019/08/04 Sun 19:12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2019/08/04 Sun 19:13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2019/08/04 Sun 19:18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2019/08/04 Sun 19:19[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2019/08/04 Sun 19:20[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2019/08/04 Sun 19:26[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2019/08/04 Sun 19:27[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2019/08/04 Sun 19:28[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2019/08/04 Sun 19:33[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2019/08/04 Sun 19:34[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2019/08/04 Sun 19:38[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2019/08/04 Sun 19:39[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2019/08/04 Sun 19:40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2019/08/04 Sun 19:42[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]2019/08/04 Sun 19:45[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2019/08/04 Sun 19:45[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]2019/08/04 Sun 19:46[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]2019/08/04 Sun 19:46[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]2019/08/04 Sun 19:47[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]08/08/2019 23:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]08/08/2019 23:49[/TD]
[TD]2.00[/TD]
[/TR]
</tbody>[/TABLE]

Using the formula:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]startDate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]08/08/2019 23:49[/TD]
[TD]=1+ROUNDDOWN((A2-startDate)/(15/24/60),0)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

shonaart

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