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

 

[Oorang]

You could do something to the effect of
IF(AND(WEEKDAY(B2,1)=7,D2>=15),D2-15,0)

 

[Arnolf]

Nope. It doesn't seem to work.
Thanks.

 

[Oorang]

Adjust to match the format that your time is in. You might need to nest the times in TIMEVALUE function. But it will definatly work.

 

[Arnolf]

Ok. Perhaps I am missing something here. (I wish I was more creative )
I enclose the Sheet with your formula.
One question: Why the reference of cell D2 ?

sphours.xls
	A	B	C	D	E	F	G
1	Saturday	Monday
2	15:00	07:00				RESULTS	YOUR
3						REQUIRED	FORMULA
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	0
6	15/09/2005	21:00	17/09/2005	16:00	43:00	01:00	0
7	16/09/2005	14:00	17/09/2005	20:00	30:00	05:00	0
8	17/09/2005	11:00	18/09/2005	19:00	32:00	28:00	0
9	17/09/2005	15:00	19/09/2005	07:00	40:00	40:00	0
10	17/09/2005	15:00	19/09/2005	09:00	42:00	40:00	0
11	18/09/2005	08:00	19/09/2005	07:00	23:00	23:00	0
12	19/09/2005	05:00	19/09/2005	18:00	13:00	02:00	0
13	23/09/2005	15:00	04/10/2005	11:00	260:00	80:00	0
14	05/10/2005	08:00	27/10/2005	17:00	537:00	120:00	0
15	10/10/2005	19:00	22/10/2005	16:00	285:00	41:00	0
16	12/10/2005	13:00	27/12/2005	10:00	1821:00	440:00	0
Sheet1

 

[Oorang]

In this example the formula in g5 should be
=IF(AND(WEEKDAY(B$5,1)=7,D$5>=15),D$5-15,0)
If that does not work do this:
Highlight Column D change the formatting to "General" and tell me what the value in D5 is.

 

[Arnolf]

Nope. I still don't get the results.
Enclose the Sheet with column D (formatting "General").

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

 

[Oorang]

Try:
IF(AND(WEEKDAY(B2,1)=7,D2>=TIMEVALUE("15:00"),D2-TIMEVALUE("15:00"),0)

 

[Arnolf]

Thank you for your reply.
Now, I am seeing values. But, the formula shows some unexpected results (cells color pink).

sphours.xls
	A	B	C	D	E	F	G
1	Saturday	Monday
2	15:00	07:00				RESULTS	YOUR
3						REQUIRED	FORMULA
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	00:00
6	15/09/2005	21:00	17/09/2005	16:00	43:00	01:00	01:00
7	16/09/2005	14:00	17/09/2005	20:00	30:00	05:00	05:00
8	17/09/2005	11:00	18/09/2005	19:00	32:00	28:00	04:00
9	17/09/2005	15:00	19/09/2005	07:00	40:00	40:00	00:00
10	17/09/2005	15:00	19/09/2005	09:00	42:00	40:00	00:00
11	18/09/2005	08:00	19/09/2005	07:00	23:00	23:00	00:00
12	19/09/2005	05:00	19/09/2005	18:00	13:00	02:00	03:00
13	23/09/2005	15:00	04/10/2005	11:00	260:00	80:00	00:00
14	05/10/2005	08:00	27/10/2005	17:00	537:00	120:00	02:00
15	10/10/2005	19:00	22/10/2005	16:00	285:00	41:00	01:00
16	12/10/2005	13:00	27/12/2005	10:00	1821:00	440:00	00:00
Sheet1

 

[Oorang]

When I was going to troubleshoot this I notice something interesting.
The value in cell E5 shows 46. When I use your time formula I show 22. How do you have E5 formatted?