A Formula to calculate Special Hours

[Arnolf]

Hello
I need to calculate Special Hours between 2 dates.
Special hours start Saturdays at 15:00 and end Mondays at 7:00 .
Attached is my Sheet with the results required in Column F.
I'd appreciate your help.
Arnolf

sphours.xls
	A	B	C	D	E	F
1	Saturday	Monday
2	15:00	07:00
3
4	Date 1	Start Hour	Date 2	End Hour	Total Hours	Special Hours
5	15/09/2005	17:00	17/09/2005	15:00	46:00	00:00
6	15/09/2005	21:00	17/09/2005	16:00	43:00	01:00
7	16/09/2005	14:00	17/09/2005	20:00	30:00	05:00
8	17/09/2005	11:00	18/09/2005	19:00	32:00	28:00
9	17/09/2005	15:00	19/09/2005	07:00	40:00	40:00
10	17/09/2005	15:00	19/09/2005	09:00	42:00	40:00
11	18/09/2005	08:00	19/09/2005	07:00	23:00	23:00
12	19/09/2005	05:00	19/09/2005	18:00	13:00	02:00
13	23/09/2005	15:00	04/10/2005	11:00	260:00	80:00
14	05/10/2005	08:00	27/10/2005	17:00	537:00	120:00
15	10/10/2005	19:00	22/10/2005	16:00	285:00	41:00
16	12/10/2005	13:00	27/12/2005	10:00	1821:00	440:00
Sheet1

 

[Arnolf]

Well, column E is formatted: [hh]:mm
Appreciate your very kind assistance.

 

[barry houdini]

You could do something like this.....

assumes all your times are round hours although it could be adjusted if this isn't the case

Book1.xls
	A	B	C	D	E	F	G
1	Saturday	Monday
2	15:00	07:00
3
4	date 1	start hour	date 2	end hour	total hours	special hours
5	15/09/2005	17:00	17/09/2005	15:00	46:00	0:00
6	15/09/2005	21:00	17/09/2005	16:00	43:00	1:00
7	16/09/2005	14:00	17/09/2005	20:00	30:00	5:00
8	17/09/2005	11:00	18/09/2005	19:00	32:00	28:00
9	17/09/2005	15:00	19/09/2005	07:00	40:00	40:00
10	17/09/2005	15:00	19/09/2005	09:00	42:00	40:00
11	18/09/2005	08:00	19/09/2005	07:00	23:00	23:00
12	19/09/2005	05:00	19/09/2005	18:00	13:00	2:00
13	23/09/2005	15:00	04/10/2005	11:00	260:00	80:00
14	05/10/2005	08:00	27/10/2005	17:00	537:00	120:00
15	10/10/2005	19:00	22/10/2005	16:00	285:00	41:00
16	12/10/2005	13:00	27/12/2005	10:00	1821:00	440:00
17
Sheet2

A1 contains the date 07/01/1900 formatted as "dddd"

B1 contains the date 09/01/1900 formatted as "dddd"

F6 copied down

=SUMPRODUCT(--(ROUND(MOD(A5+B5-A$1-A$2+(ROW(INDIRECT("1:"&ROUND((D5+C5-B5-A5)*24,0)))-1)/24,7),9)< ROUND(B$1+B$2-A$1-A$2,9)))/24

 

[Arnolf]

Yes!!! Barry.
That is exactly what I am looking for.
As you said, it works perfect with rounded hours.

Only one last favor.
How would be the formula to work with all times ?. (rounded and not rounded hours).

Again, Thank you for your kind help.
Arnolf.

 

[barry houdini]

For times shown to the nearest minute (in F5, of course, not F6)

=SUMPRODUCT(--(ROUND(MOD(A5+B5-A$1-A$2+(ROW(INDIRECT("1:"&ROUND((D5+C5-B5-A5)*1440,0)))-1)/1440,7),9)< ROUND(B$1+B$2-A$1-A$2,9)))/1440

although this will only work if your time periods are less than approx 45 days, i.e. it won't work for your row 16.

If that's a problem then change the three 1440s to 288s for times shown to 5 minutes

 

[Arnolf]

Barry,
More than happy with your assistance.
One more time. I appreciate your very kind help.
rgds,
Arnolf.