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]

Here is the workbook with dates and times:
You can format column A to just show times. .. but you must input the date as well.

Mr Excel Questions2.xlsm
	A	B
1	Arr	Transport Time
2	02/06/2023 10:03 PM	10:03:51 PM
3	02/06/2023 11:12 PM	11:12:38 PM
4	02/06/2023 11:22 PM	11:12:38 PM
5	02/06/2023 11:27 PM	11:12:38 PM
6	02/06/2023 11:29 PM	11:12:38 PM
7	02/06/2023 11:47 PM	11:47:08 PM
8	02/06/2023 11:48 PM	11:47:08 PM
9	02/06/2023 11:50 PM	11:47:08 PM
10	02/06/2023 11:54 PM	11:54:56 PM
11	02/06/2023 11:59 PM	11:54:56 PM
12	02/07/2023 12:23 AM	11:54:56 PM
13	02/07/2023 12:29 AM	11:54:56 PM
14	02/07/2023 12:29 AM	11:54:56 PM
15	02/07/2023 12:30 AM	11:54:56 PM
16	02/07/2023 12:39 AM	11:54:56 PM
17	02/07/2023 12:42 AM	12:42:31 AM
18	02/07/2023 12:46 AM	12:42:31 AM
19	02/07/2023 12:50 AM	12:42:31 AM
20	02/07/2023 12:54 AM	12:42:31 AM
21	02/07/2023 12:54 AM	12:42:31 AM
22	02/08/2023 01:01 AM	1:01:06 AM
23	02/08/2023 01:02 AM	1:01:06 AM
24	02/08/2023 01:41 AM	1:41:21 AM
25	02/08/2023 01:43 AM	1:41:21 AM
26	02/08/2023 01:44 AM	1:41:21 AM
45 Minute Time Schedule Tranchs

Cell Formulas
Range	Formula
B2,B4:B6,B8:B9,B11:B23,B25:B26	B2	=IF(ISNUMBER(B1)=FALSE,A2,IF(A2<=(B1+45/(60*24)),B1,A2))
B3,B7,B10,B24	B3	=IF(ISNUMBER(#REF!)=FALSE,A3,IF(A3<=(#REF!+45/(60*24)),#REF!,A3))

 

[MaayanYogev]

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.

Yes, I believe this is the problem
adding a picture without colors
*in red- the beginning of the problem

 

[awoohaw]

Yes, I believe this is the problem
adding a picture without colors
*in red- the beginning of the problem

Can you look at what I just sent, I think if you use dates as input you will work well. If not, please let me know.

 

[MaayanYogev]

Here is the workbook with dates and times:
You can format column A to just show times. .. but you must input the date as well.

Mr Excel Questions2.xlsm
	A	B
1	Arr	Transport Time
2	02/06/2023 10:03 PM	10:03:51 PM
3	02/06/2023 11:12 PM	11:12:38 PM
4	02/06/2023 11:22 PM	11:12:38 PM
5	02/06/2023 11:27 PM	11:12:38 PM
6	02/06/2023 11:29 PM	11:12:38 PM
7	02/06/2023 11:47 PM	11:47:08 PM
8	02/06/2023 11:48 PM	11:47:08 PM
9	02/06/2023 11:50 PM	11:47:08 PM
10	02/06/2023 11:54 PM	11:54:56 PM
11	02/06/2023 11:59 PM	11:54:56 PM
12	02/07/2023 12:23 AM	11:54:56 PM
13	02/07/2023 12:29 AM	11:54:56 PM
14	02/07/2023 12:29 AM	11:54:56 PM
15	02/07/2023 12:30 AM	11:54:56 PM
16	02/07/2023 12:39 AM	11:54:56 PM
17	02/07/2023 12:42 AM	12:42:31 AM
18	02/07/2023 12:46 AM	12:42:31 AM
19	02/07/2023 12:50 AM	12:42:31 AM
20	02/07/2023 12:54 AM	12:42:31 AM
21	02/07/2023 12:54 AM	12:42:31 AM
22	02/08/2023 01:01 AM	1:01:06 AM
23	02/08/2023 01:02 AM	1:01:06 AM
24	02/08/2023 01:41 AM	1:41:21 AM
25	02/08/2023 01:43 AM	1:41:21 AM
26	02/08/2023 01:44 AM	1:41:21 AM
45 Minute Time Schedule Tranchs

Cell Formulas
Range	Formula
B2,B4:B6,B8:B9,B11:B23,B25:B26	B2	=IF(ISNUMBER(B1)=FALSE,A2,IF(A2<=(B1+45/(60*24)),B1,A2))
B3,B7,B10,B24	B3	=IF(ISNUMBER(#REF!)=FALSE,A3,IF(A3<=(#REF!+45/(60*24)),#REF!,A3))

Thank you so so much!!

 

[awoohaw]

My pleasure.