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

 

[MaayanYogev]

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?

Im working on this sheet to manage vehicle drivers to pick up people from the airport and schedule it by their flight landing time.
The role is that parties can only wait at the airport for a maximum of 45 minutes.
So- transportation pick-up time should be the minimum at a range.
If I would manually set the hours ("Transportation Time" column), it would look like this in the picture:

 

[awoohaw]

To put it words I may understand better.
You want to restart the 45 minute window at the point of the first arrival AFTER the prior 45 minute window has expired? Adn that restart time is the arrival time?

 

[awoohaw]

How is this:

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

Cell Formulas
Range	Formula
B2	B2	=IF(ISNUMBER(B1)=FALSE,A2,IF(A2<=(B1+45/(60*24)),B1,A2+(45/(24*60))))
B3:B13	B3	=IF(A3<=(B2+45/(60*24)),B2,A3+(45/(24*60)))
A10:A11	A10	=TIME(16,40,0)
A12	A12	=TIME(17,10,0)
A13	A13	=TIME(17,20,0)

 

[MaayanYogev]

To put it words I may understand better.
You want to restart the 45 minute window at the point of the first arrival AFTER the prior 45 minute window has expired? Adn that restart time is the arrival time?

Yes!
Im looking to open a new 45-minute window every time, each by the earliest arrival time of the group.
Refer to your example- B8:B9 should be 15:06, and B10:B13 should be 16:40

 

[awoohaw]

Okay, I think this is it:

Mr Excel Questions2.xlsm
	A	B	C
1	Arr	Transport Time
2	14:05	14:05
3	14:12	14:05
4	14:18	14:05
5	14:18	14:05
6	14:36	14:05
7	14:45	14:05
8	15:06	15:06
9	15:26	15:06
10	16:40	16:40
11	16:40	16:40
12	17:10	16:40
13	17:20	16:40
14
45 Minute Time Schedule Tranchs

Cell Formulas
Range	Formula
B2:B13	B2	=IF(ISNUMBER(B1)=FALSE,A2,IF(A2<=(B1+45/(60*24)),B1,A2))
A10:A11	A10	=TIME(16,40,0)
A12	A12	=TIME(17,10,0)
A13	A13	=TIME(17,20,0)

 

[MaayanYogev]

Okay, I think this is it:

Mr Excel Questions2.xlsm
	A	B	C
1	Arr	Transport Time
2	14:05	14:05
3	14:12	14:05
4	14:18	14:05
5	14:18	14:05
6	14:36	14:05
7	14:45	14:05
8	15:06	15:06
9	15:26	15:06
10	16:40	16:40
11	16:40	16:40
12	17:10	16:40
13	17:20	16:40
14
45 Minute Time Schedule Tranchs

Cell Formulas
Range	Formula
B2:B13	B2	=IF(ISNUMBER(B1)=FALSE,A2,IF(A2<=(B1+45/(60*24)),B1,A2))
A10:A11	A10	=TIME(16,40,0)
A12	A12	=TIME(17,10,0)
A13	A13	=TIME(17,20,0)

WOW!
Thank you so much!
I highly appreciate that

 

[awoohaw]

My Pleasure. Have a great day!

 

[MaayanYogev]

My Pleasure. Have a great day!

Hey!
I have an issue
The formula doesn't work when the hour is after 23:59 (or maybe it's because of the date change?)
adding a picture:
*14.02 02:20 is the first cell that his transportation time is not working well



Thank you so much!!

 

[awoohaw]

the problem is with ARR times 23:15 thru 24:00? if that statment is wrong, please let me know.
Your image is too dark to understand. I'll look at it.

 

[awoohaw]

Well, the easy answer is to include the Date in the arrival time.

Keyboard short cut to insert Date and Time:
Step 1: Press Cntl and semi colon simultaneously.
Step 2: type a space
Step 3: Press Cntl, Shift, and semi colon simultaneously
Step 4: Press Enter.

You could use the now() function, but that changes every everytime your workbook recalculates.

A hard answer would involve some date mathmatics!