Formula problem: Timesplit

[blaksnm]

Hi
After some hours of strugling I have to ask for help:
I have a time-registration-schedule with "Ins and "Out"s - (cells A2:An and B2:Bn)
This time-summary is to be split accordingly in 4 "sones" by formulas; 00:00->08:00 (Cells C2:Cn) / 08:00=->16:00 (Cells D2:Dn) / 16:00=>20:00 (Cells E2:En)and 20:00=>24:00 (Cells F2:Fn)

Will anyone please give me a hand to get the formulas right?

Best Regards
Snoopy

 

[Toadstool]

Hi Snoopy,

I know sones as a measure of volume so I'm a bit confused. I suspect you want the in time and out time split into minutes overlapped with your time ranges? If so can the Out time be less than the In time, suggesting the Out is the next day?

Some worked examples would be helpful, preferably using XL2BB

 

[blaksnm]

Thanks for taking interest in this problem

I will download XL2BB
Meanwhile: Her is my expectet results (Sone=time interval, Result=time-summary formatted as decimals):

Some examples:
Input time I-- Sone1 ---I--- Sone2 ---I-- - Sone3 ---I--- Sone4 ---I
Column A B C D E F G
Row 01 In Out 00:00-08:00 08:00-16:00 16:00-20:00 16:00-20:00 I--- Total ---I
Row 02 07:30 07:45 0,25 0,00 0,00 0,00 0,25
Row 03 07:45 08:30 0, 25 0,50 0,00 0,00 0,75
Row 04 07:00 16:15 1,00 8,00 0,25 0,00 0,00
Row 05 15:00 16:15 0,00 1,00 0,25 0,00 0,00
Row 06 15:00 20:30 0,00 1,00 4,00 0,50 0,00
Row 07 20:15 20:30 0,00 0,00 0,00 0,25 0,00
Row 07 22:15 22:30 0,00 0,00 0,00 0,25 0,00

I hope this will clarify a bit

Best Regards Snoopy

 

[blaksnm]

Thanks for taking interest in this problem

I will download XL2BB
Meanwhile: Her is my expectet results (Sone=time interval, Result=time-summary formatted as decimals):
Problem: design formulas that split the registrated use of time into time-intervals as followed

Some examples:

Reg In​	Reg Out​	00:00-08:00​	08:00-16:00​	16:00-20:00 ​	16:00-20:00​	20:00-24:00​	Total ​
07:30​	07:45​	0,25​	​	​	​	​	0,25​
07:00​	08:30​	1,00​	0,50​	​	​	​	1,50​
06:00​	16:15​	2,00​	8,00​	0,25​	​	​	11,25​
15:00​	17:15​	​	1,00​	1,25​	​	​	2,25​
20:15​	20:30​	​	​	​	0,25​	​	0,25​
22:15​	23:30​					1,25​	1,25​
7:45​	23:30​	0,25​	8,00​	4,00​	4,00​	3,50​	19,75​

I hope this will clarify a bit

Best Regards Snoopy

 

[Toadstool]

Hi Snoopy,

You didn't answer about overlapping days, i.e. Reg Out time being less than Reg In time suggesting it overlaps midnight. This solution does not address that scenario.

There is no such time as 24:00 so I've used 23:59:59 as the last upper bound.

You had two entries for 16:00-20:00 so I've split that into 16:00-20:00, 20:00-22:00 and made the last bound 22:00-23:59.

My calculations disagree with your example in two cases: Rows 5 and 9.

Excel does not do well interpreting strings as numbers so I've split the lower/upper bounds into two rows.

NOTE: You can suppress the 0:00 cells by using custom format 0.00;;#

Blacksnm.xlsx
	A	B	C	D	E	F	G	H
1			0:00	8:00	16:00	20:00	22:00
2	Reg In	Reg Out	8:00	16:00	20:00	22:00	23:59	Total
3	7:30	7:45	0.25	0.00	0.00	0.00	0.00	0.25
4	7:00	8:30	1.00	0.50	0.00	0.00	0.00	1.50
5	6:00	16:15	2.00	8.00	0.25	0.00	0.00	10.25
6	15:00	17:15	0.00	1.00	1.25	0.00	0.00	2.25
7	20:15	20:30	0.00	0.00	0.00	0.25	0.00	0.25
8	22:15	23:30	0.00	0.00	0.00	0.00	1.25	1.25
9	7:45	23:30	0.25	8.00	4.00	2.00	1.50	15.75
1st

Cell Formulas
Range	Formula
C3:G9	C3	=(MAX(MIN(C$2,$B3)-MAX(C$1,$A3),0))*24
H3:H9	H3	=SUM(C3:G3)

 

[blaksnm]

Hi Snoopy,

You didn't answer about overlapping days, i.e. Reg Out time being less than Reg In time suggesting it overlaps midnight. This solution does not address that scenario.

There is no such time as 24:00 so I've used 23:59:59 as the last upper bound.

You had two entries for 16:00-20:00 so I've split that into 16:00-20:00, 20:00-22:00 and made the last bound 22:00-23:59.

My calculations disagree with your example in two cases: Rows 5 and 9.

Excel does not do well interpreting strings as numbers so I've split the lower/upper bounds into two rows.

NOTE: You can suppress the 0:00 cells by using custom format 0.00;;#

Blacksnm.xlsx
	A	B	C	D	E	F	G	H
1			0:00	8:00	16:00	20:00	22:00
2	Reg In	Reg Out	8:00	16:00	20:00	22:00	23:59	Total
3	7:30	7:45	0.25	0.00	0.00	0.00	0.00	0.25
4	7:00	8:30	1.00	0.50	0.00	0.00	0.00	1.50
5	6:00	16:15	2.00	8.00	0.25	0.00	0.00	10.25
6	15:00	17:15	0.00	1.00	1.25	0.00	0.00	2.25
7	20:15	20:30	0.00	0.00	0.00	0.25	0.00	0.25
8	22:15	23:30	0.00	0.00	0.00	0.00	1.25	1.25
9	7:45	23:30	0.25	8.00	4.00	2.00	1.50	15.75
1st

Cell Formulas
Range	Formula
C3:G9	C3	=(MAX(MIN(C$2,$B3)-MAX(C$1,$A3),0))*24
H3:H9	H3	=SUM(C3:G3)

Sorry
No overlapping days - timeregistration is day-by-day only
Br S

 

[blaksnm]

Hi Snoopy,

You didn't answer about overlapping days, i.e. Reg Out time being less than Reg In time suggesting it overlaps midnight. This solution does not address that scenario.

There is no such time as 24:00 so I've used 23:59:59 as the last upper bound.

You had two entries for 16:00-20:00 so I've split that into 16:00-20:00, 20:00-22:00 and made the last bound 22:00-23:59.

My calculations disagree with your example in two cases: Rows 5 and 9.

Excel does not do well interpreting strings as numbers so I've split the lower/upper bounds into two rows.

NOTE: You can suppress the 0:00 cells by using custom format 0.00;;#

Blacksnm.xlsx
	A	B	C	D	E	F	G	H
1			0:00	8:00	16:00	20:00	22:00
2	Reg In	Reg Out	8:00	16:00	20:00	22:00	23:59	Total
3	7:30	7:45	0.25	0.00	0.00	0.00	0.00	0.25
4	7:00	8:30	1.00	0.50	0.00	0.00	0.00	1.50
5	6:00	16:15	2.00	8.00	0.25	0.00	0.00	10.25
6	15:00	17:15	0.00	1.00	1.25	0.00	0.00	2.25
7	20:15	20:30	0.00	0.00	0.00	0.25	0.00	0.25
8	22:15	23:30	0.00	0.00	0.00	0.00	1.25	1.25
9	7:45	23:30	0.25	8.00	4.00	2.00	1.50	15.75
1st

Cell Formulas
Range	Formula
C3:G9	C3	=(MAX(MIN(C$2,$B3)-MAX(C$1,$A3),0))*24
H3:H9	H3	=SUM(C3:G3)

I think you just made my day
Thank you so very much!

 

[Toadstool]

I think you just made my day
Thank you so very much!

You're very welcome.
Thanks for the feedback.