Formula to Split Start/End Times into Hour Brackets

[MrMaker]

Good Morning,

I have a set of data with start/end dates which I am trying to split into hour categories so I can dissect further (i.e if a line starts at 5am and ends at 7am I would expect to see 1 hour in the 5 and 6 am columns)
My formula splits things up nicely but when the end date falls in the next day it still includes it with the same line (see highlighted yellow line).
Is there a solution that anyone knows or can suggest that I have overlooked?

The generic formula I found on here to split the hours up is this:

    =(NETWORKDAYS(B5,C5)-1)*($G$3-$G$2)
+IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),$G$3,$G$2),$G$3)
-MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),$G$3,$G$2)

Many thanks

 

[6StringJazzer]

What is does currently makes perfect sense to me. The correct times fall under the correct hours on the same row that your start/end times appear. It wouldn't make any sense to me to show it on any other row. How do you want this to appear instead?

 

[MrMaker]

Hi Jeff,

Not a great explanation from myself, apologies.

Essentially, I was looking for a daily summary view of the data, i.e only calculating the hours from 00:00 until 23:59 of the start date (08/07/24) ......with the 00:00 to 06:31 (09/07/24) somehow being represented on a daily summary for 09/07/24.

95% of the data has the same start and end date, it's just the 5% that skews things from a daily summary point of view.

I'm beginning to think it's probably not doable without major manual intervention.

Thanks

 

[6StringJazzer]

How are you getting a daily summary when each block of time appears on its own row? That is, there are multiple rows for each day. I don't see a summary per day in your screenshot. Do you need the detail of hours broken out for each set of times, or just the totals per day?

I'm sure this can all be done automatically but you would probably need a redesign. Something like this layout, but I haven't worked out formulas.

$scratch.xlsm
	A	B
1	Start	End
2	7/8/2024 15:00	7/8/2024 16:30
3	7/8/2024 15:00	7/8/2024 16:30
4	7/8/2024 15:00	7/8/2024 16:30
5	7/8/2024 15:00	7/8/2024 16:30
6	7/8/2024 15:00	7/8/2024 16:30
7	7/8/2024 15:00	7/9/2024 06:30
8	7/9/2024 06:30	7/9/2024 07:30
9	7/9/2024 06:30	7/9/2024 07:30
10	7/9/2024 06:30	7/9/2024 07:30
Sheet9

$scratch.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Date	00:00:00	01:00:00	02:00:00	03:00:00	04:00:00	05:00:00	06:00:00	07:00:00	08:00:00	09:00:00	10:00:00	11:00:00	12:00:00	13:00:00	14:00:00	15:00:00	16:00:00	17:00:00	18:00:00	19:00:00	20:00:00	21:00:00	22:00:00	23:00:00
2	7/8/2024																06:00:00	03:30:00	01:00:00	01:00:00	01:00:00	01:00:00	01:00:00	01:00:00	01:00:00
3	7/9/2024							00:30:00	00:30:00
Sheet9

Cell Formulas
Range	Formula
F1:AB1	F1	=E1+(TIME(1,0,0))

 

[felixstraube]

Could you explain what the end result you need is? Maybe there is another way.