Time intervals

[MaayanYogev]

Time intervals
I have a database of flight arrival times and would like to group them into intervals of 45 minutes, starting from the first hour.
FLOORfunction is problematic here because it rounds down the minutes.

For example:

14:05:00 -- I need the interval to start at 14:05, but "FLOOR" starts if from 14:00
14:12:00
14:18:00
14:45:00

How can I solve this issue?

Thanks

 

[awoohaw]

I'm sure there is a better way, but this will give you the 'Every 45 minute start time'
2:00 PM is not evenly divisible by :45 minutes, so this start time is 1:30.


=(45*(INT((HOUR(A2)*60+MINUTE(A2))/45))/(60*24))

 

[James006]

Hi,

What is the significance you are using in your Floor function ....?
You could test : 0.0342

 

[awoohaw]

I think this will get you with 45 min intervals starting at 2:00 PM
=((45*(INT((HOUR(A2)*60+MINUTE(A2))/45)))/(60*24))+IF(MOD((HOUR(A2)*60+MINUTE(A2)),45)>=30,1/48,0)
I was wrong.

 

[awoohaw]

Here it is:

Mr Excel Questions2.xlsm
	A	B
1
2	2:05:00 PM	2:00:00 PM
3	2:12:00 PM	2:00:00 PM
4	2:18:00 PM	2:00:00 PM
5	2:18:07 PM	2:00:00 PM
6	2:36:11 PM	2:00:00 PM
7	2:45:00 PM	2:45:00 PM
8	3:06:25 PM	2:45:00 PM
9	3:26:51 PM	2:45:00 PM
Sheet6

Cell Formulas
Range	Formula
B2:B9	B2	=((45*(INT((HOUR(A2)*60+MINUTE(A2))/45)))/(60*24))+IF(MOD((HOUR(A2)*60+MINUTE(A2)),45)>=30,1/48,-0.5/48)

 

[MaayanYogev]

Thank you all!
It's still not the exact result I'm looking for,
adding a picture of the current values that I'm getting with 'FLOOR'- on red, and on green- the desired outcome (I simulated a few rows manually)
*the operation is on the Arv. Time column

 

[awoohaw]

Were you getting any errors with this:

=((45*(INT((HOUR(A2)*60+MINUTE(A2))/45)))/(60*24))+IF(MOD((HOUR(A2)*60+MINUTE(A2)),45)>=30,1/48,-0.5/48)

 

[awoohaw]

Thank you all!
It's still not the exact result I'm looking for,
adding a picture of the current values that I'm getting with 'FLOOR'- on red, and on green- the desired outcome (I simulated a few rows manually)
*the operation is on the Arv. Time column

View attachment 84654

I don't understand what you are asking for, then. Your desired results are not in multiples of 45 minutes starting at 2:00 pm?

 

[MaayanYogev]

Were you getting any errors with this:

=((45*(INT((HOUR(A2)*60+MINUTE(A2))/45)))/(60*24))+IF(MOD((HOUR(A2)*60+MINUTE(A2)),45)>=30,1/48,-0.5/48)

Thank you so much!
That works with no errors, but the issue is splitting big data into 45 minutes intervals when the first value is not always the same.. for example, look at the picture Im sending- I need the function to "understand" to put the 'arv. Time' of 16:40 with 17:10 and 17:20 at the same 'Transportation time' (16:40).
**functions are on the 'Transportation Time' column

 

[awoohaw]

Thank you so much!
That works with no errors, but the issue is splitting big data into 45 minutes intervals when the first value is not always the same.. for example, look at the picture Im sending- I need the function to "understand" to put the 'arv. Time' of 16:40 with 17:10 and 17:20 at the same 'Transportation time' (16:40).
**functions are on the 'Transportation Time' column
View attachment 84658

That is contrary to your requirement. the 45 Minute Interval groups (starting at 2:00 PM) are:

Mr Excel Questions2.xlsm
	A	B	C
1	Arr	Start of Time Group	End of Time Group
2	14:05	14:00	14:45
3	14:12	14:00	14:45
4	14:18	14:00	14:45
5	14:18	14:00	14:45
6	14:36	14:00	14:45
7	14:45	14:45	15:30
8	15:06	14:45	15:30
9	15:26	14:45	15:30
10	16:40	16:15	17:00
11	16:40	16:15	17:00
12	17:10	17:00	17:45
13	17:20	17:00	17:45
Sheet6

Cell Formulas
Range	Formula
B2:B13	B2	=((45*(INT((HOUR(A2)*60+MINUTE(A2))/45)))/(60*24))+IF(MOD((HOUR(A2)*60+MINUTE(A2)),45)>=30,1/48,-0.5/48)
C2:C13	C2	=B2+45/(24*60)
A10:A11	A10	=TIME(16,40,0)
A12	A12	=TIME(17,10,0)
A13	A13	=TIME(17,20,0)

Please explain why I am thinking differently than you?