Calculating hours between 00:00-06:00 and 17:00-00:00 and result total in decimal hours

[Want2BExcel]

Help me solve this.
The set-up is as follows.
A1 (weekday)
B1 (date)
D1 (time,start1)
E1 (time,end1)
F1 (time, catagory1)
G1 (time,start2)
H1 (time,end2)
I1 (time, catagory2)

I want to make a formula that count hours between 00:00-06:00 and 17:00-00:00 when time is entered in D1,E1,G1,H1
Ex: D1=00:00 E1=09:00 G1=22:30 H1=00:00 Result 7,5
I looked at a former tread about some ATM's but couldn't convert the formula to my needs.
Hope someone can help.

Regards
Kenneth

 

[Toadstool]

Hi Want2BExcel,

Wouldn't 00:00 to 09:00 be 9 hours and 22:30 to 00:00 be 1.5 hours so total 10.5 hours?

Excel holds dates as integer days since 1 Jan 1900 and times as a fraction of a day (e.g. 12 noon is 0.5, 06:00am is 0.25).
As the day starts at 00:00 then there is a problem with the second end time and the solution is to add a day, i.e. +1. You can then do the maths to get the total time but must multiply by 24 to get that fraction of a day expressed as decimal hours.

Assuming the full work day never exceeds 47.999 hours then make sure you format the result cell as General or Number and try:

=24*(IF(D1<C1,(D1+1-C1),(D1-C1))+IF(G1<F1,(G1+1-F1),(G1-F1)))

 

[Want2BExcel]

Hi Want2BExcel,

Wouldn't 00:00 to 09:00 be 9 hours and 22:30 to 00:00 be 1.5 hours so total 10.5 hours?

Excel holds dates as integer days since 1 Jan 1900 and times as a fraction of a day (e.g. 12 noon is 0.5, 06:00am is 0.25).
As the day starts at 00:00 then there is a problem with the second end time and the solution is to add a day, i.e. +1. You can then do the maths to get the total time but must multiply by 24 to get that fraction of a day expressed as decimal hours.

Assuming the full work day never exceeds 47.999 hours then make sure you format the result cell as General or Number and try:

=24*(IF(D1<C1,(D1+1-C1),(D1-C1))+IF(G1<F1,(G1+1-F1),(G1-F1)))

In total it is 10,5 hours, but it should only count the hours from 00:00 to 06:00 and the hours from 17:00 to 00:00. So if it possible to make a formula to that, it should give the result 7,5 hours, since hours from 06:00 to 09:00 is ignored

 

[Toadstool]

Please test this:

=(IF(C1>TIME(6,0,0),0,MAX(MIN(IF(D1<C1,1,D1),TIME(6,0,0))-MAX(C1,TIME(0,0,0),0)))+MAX(MIN(IF(D1<C1,1,D1),1)-MAX(C1,TIME(17,0,0)),0))+(IF(F1>TIME(6,0,0),0,MAX(MIN(IF(G1<F1,1,G1),TIME(6,0,0))-MAX(F1,TIME(0,0,0),0)))+MAX(MIN(IF(G1<F1,1,G1),1)-MAX(F1,TIME(17,0,0)),0))

 

[Want2BExcel]

Please test this:

=(IF(C1>TIME(6,0,0),0,MAX(MIN(IF(D1<C1,1,D1),TIME(6,0,0))-MAX(C1,TIME(0,0,0),0)))+MAX(MIN(IF(D1<C1,1,D1),1)-MAX(C1,TIME(17,0,0)),0))+(IF(F1>TIME(6,0,0),0,MAX(MIN(IF(G1<F1,1,G1),TIME(6,0,0))-MAX(F1,TIME(0,0,0),0)))+MAX(MIN(IF(G1<F1,1,G1),1)-MAX(F1,TIME(17,0,0)),0))

Just to be clear, because I'm a little confused, since you a using C1 (empty) and F1 (time, catagory1)
By * you are meaning (as follows):
*C1=(time,start1) whitch would make
*D1=(time,end1)
*F1 (time,start2)
*G1 (time,end2)
With that in mind I get result 0,06 not 7,5

this is the formula I got from you with my actual cell references:
=(IF([@Start1]>TIME(6;0;0);0;MAX(MIN(IF([@End1]<[@Start1];1;[@End1]);TIME(6;0;0))-MAX([@Start1];TIME(0;0;0);0)))+MAX(MIN(IF([@End1]<[@Start1];1;[@End1]);1)-MAX([@Start1];TIME(17;0;0));0))+(IF([@Start2]>TIME(6;0;0);0;MAX(MIN(IF([@End2]<[@Start2];1;[@End2]);TIME(6;0;0))-MAX([@Start2];TIME(0;0;0);0)))+MAX(MIN(IF([@End2]<[@Start2];1;[@End2]);1)-MAX([@Start2];TIME(17;0;0));0))

[@Start1] = 00:00 (*C1)
[@End1] = 09:00 (*D1)
[@Start2] = 22:30 (*F1)
[@End2] = 00:00 (*G1)
Result is 0,06 (should be 7,5 (6+1,5))

I'm using Excel2016 therefor it's ; insted of ,

...REALLY APPRECIATE YOUR HELP!!!

 

[Toadstool]

It seems to work for me. I also have Excel 2016 but my Windows Control Panel has Region settings for the US.

Want2BExcel.xlsx
	A	B	C	D	E	F	G	H	I
1	Weekday	Date	Start1	End1	Category1	Start2	End2	Category2	Result
2	Mon	11/1/2021	0:00	9:00		22:30	0:00		7:30
3	Tue	11/2/2021	0:00	0:00		14:00	0:00		7:00
4	Wed	11/3/2021	4:00	11:00		14:00	23:30		8:30
5	Thu	11/4/2021	3:30	5:00		19:00	21:30		4:00
6	Fri	11/5/2021	1:00	5:00		16:00	18:00		5:00
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=TEXT(B2,"ddd")
I2:I6	I2	=(IF([@Start1]>TIME(6,0,0),0,MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),TIME(6,0,0))-MAX([@Start1],TIME(0,0,0),0)))+MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),1)-MAX([@Start1],TIME(17,0,0)),0))+(IF([@Start2]>TIME(6,0,0),0,MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),TIME(6,0,0))-MAX([@Start2],TIME(0,0,0),0)))+MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),1)-MAX([@Start2],TIME(17,0,0)),0))

Named Ranges
Name	Refers To	Cells
Date	=Sheet1!$B$2	A2
End1_	=Sheet1!$D$2	I2
End2_	=Sheet1!$G$2	I2
Start1	=Sheet1!$C$2	I2
Start2	=Sheet1!$F$2	I2

 

[Want2BExcel]

It seems to work for me. I also have Excel 2016 but my Windows Control Panel has Region settings for the US.

Want2BExcel.xlsx
	A	B	C	D	E	F	G	H	I
1	Weekday	Date	Start1	End1	Category1	Start2	End2	Category2	Result
2	Mon	11/1/2021	0:00	9:00		22:30	0:00		7:30
3	Tue	11/2/2021	0:00	0:00		14:00	0:00		7:00
4	Wed	11/3/2021	4:00	11:00		14:00	23:30		8:30
5	Thu	11/4/2021	3:30	5:00		19:00	21:30		4:00
6	Fri	11/5/2021	1:00	5:00		16:00	18:00		5:00
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=TEXT(B2,"ddd")
I2:I6	I2	=(IF([@Start1]>TIME(6,0,0),0,MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),TIME(6,0,0))-MAX([@Start1],TIME(0,0,0),0)))+MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),1)-MAX([@Start1],TIME(17,0,0)),0))+(IF([@Start2]>TIME(6,0,0),0,MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),TIME(6,0,0))-MAX([@Start2],TIME(0,0,0),0)))+MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),1)-MAX([@Start2],TIME(17,0,0)),0))

Named Ranges
Name	Refers To	Cells
Date	=Sheet1!$B$2	A2
End1_	=Sheet1!$D$2	I2
End2_	=Sheet1!$G$2	I2
Start1	=Sheet1!$C$2	I2
Start2	=Sheet1!$F$2	I2

I found the error. It was the formatting of [@Start1] and the result cell.
Your [@Start1] was 00:00:00, mine was 01-01-1900 00:00:00
and
your result cell was formatted in time, mine in numbers (to get the decimal hours).

Maybe it isn't the right way to do it, but I just couldn't add the =INT function, to convert the result cell to decimal hours to your formula, so I added a colum (Result2) just to convert the result cell to decimal hours and then hide the result cell. "It isn't pretty, but it works "

 

[Toadstool]

You could do it by multiplying the current Result by 24 (as the internal representation is fractions of 1 day so 6pm is held as 0.75 so multiplying by 24 gets the decimal hours) but make sure you format as General or, as I've done, as a number with 2 decimals.

Want2BExcel.xlsx
	A	B	C	D	E	F	G	H	I
1	Weekday	Date	Start1	End1	Category1	Start2	End2	Category2	Result
2	Mon	11/1/2021	0:00	9:00		22:30	0:00		7.50
3	Tue	11/2/2021	0:00	0:00		14:00	22:45		5.75
4	Wed	11/3/2021	4:00	11:00		14:00	23:30		8.50
5	Thu	11/4/2021	3:15	5:00		19:00	21:30		4.25
6	Fri	11/5/2021	1:00	5:00		16:00	18:00		5.00
7	Sat	11/6/2021	14:00	22:00		0:00	0:00		5.00
8	Sun	11/7/2021	0:00	8:30		14:00	0:00		13.00
9	Mon	11/8/2021	1:30	12:00		13:00	18:45		6.25
2nd

Cell Formulas
Range	Formula
A2:A9	A2	=TEXT(B2,"ddd")
I2:I9	I2	=24*((IF([@Start1]>TIME(6,0,0),0,MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),TIME(6,0,0))-MAX([@Start1],TIME(0,0,0),0)))+MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),1)-MAX([@Start1],TIME(17,0,0)),0))+(IF([@Start2]>TIME(6,0,0),0,MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),TIME(6,0,0))-MAX([@Start2],TIME(0,0,0),0)))+MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),1)-MAX([@Start2],TIME(17,0,0)),0)))

Named Ranges
Name	Refers To	Cells
'2nd'!Date	='2nd'!$B$2	A2
'2nd'!End1_	='2nd'!$D$2	I2
'2nd'!End2_	='2nd'!$G$2	I2
'2nd'!Start1	='2nd'!$C$2	I2
'2nd'!Start2	='2nd'!$F$2	I2

 

[Want2BExcel]

You could do it by multiplying the current Result by 24 (as the internal representation is fractions of 1 day so 6pm is held as 0.75 so multiplying by 24 gets the decimal hours) but make sure you format as General or, as I've done, as a number with 2 decimals.

Want2BExcel.xlsx
	A	B	C	D	E	F	G	H	I
1	Weekday	Date	Start1	End1	Category1	Start2	End2	Category2	Result
2	Mon	11/1/2021	0:00	9:00		22:30	0:00		7.50
3	Tue	11/2/2021	0:00	0:00		14:00	22:45		5.75
4	Wed	11/3/2021	4:00	11:00		14:00	23:30		8.50
5	Thu	11/4/2021	3:15	5:00		19:00	21:30		4.25
6	Fri	11/5/2021	1:00	5:00		16:00	18:00		5.00
7	Sat	11/6/2021	14:00	22:00		0:00	0:00		5.00
8	Sun	11/7/2021	0:00	8:30		14:00	0:00		13.00
9	Mon	11/8/2021	1:30	12:00		13:00	18:45		6.25
2nd

Cell Formulas
Range	Formula
A2:A9	A2	=TEXT(B2,"ddd")
I2:I9	I2	=24*((IF([@Start1]>TIME(6,0,0),0,MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),TIME(6,0,0))-MAX([@Start1],TIME(0,0,0),0)))+MAX(MIN(IF([@End1]<[@Start1],1,[@End1]),1)-MAX([@Start1],TIME(17,0,0)),0))+(IF([@Start2]>TIME(6,0,0),0,MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),TIME(6,0,0))-MAX([@Start2],TIME(0,0,0),0)))+MAX(MIN(IF([@End2]<[@Start2],1,[@End2]),1)-MAX([@Start2],TIME(17,0,0)),0)))

Named Ranges
Name	Refers To	Cells
'2nd'!Date	='2nd'!$B$2	A2
'2nd'!End1_	='2nd'!$D$2	I2
'2nd'!End2_	='2nd'!$G$2	I2
'2nd'!Start1	='2nd'!$C$2	I2
'2nd'!Start2	='2nd'!$F$2	I2

Well of course, LOL! Man, I must be tired! It's beautiful, just what I wanted! THANK YOU Toadstool!!!!

 

[Toadstool]

You're welcome.
...though I'm not sure of its beauty.

I know I'm not the smartest Excel'er on the forum, and I'm just trying to answer questions to prevent my brain atrophying in retirement, but once you've rested I can try and explain the formula if you'd like. I started by looking at using IF statements but quickly became lost in an IF maze, so moved to using an overlap calculation, hence all the MIN and MAX as it's based on the common overlapping dates formula MAX(MIN(End1,End2)-MAX(Start1,Start2)+1,0)

Let me know if you like the explanation.