hey Sandy Mann

I am very sorry, but I had not seen your response here, but I will test it,
because I can not get "18:00 - 22:00" the count to work after 24:00 crossed.

Sincerely,

H. Nissen

"Sandy Mann" skrev:

No, my formuals below still give errors at some times. You are right to go
with Daddylonglegs formula in your other thread - that seems to work with
all time entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Hi,

Assuming that both D3 and E3 *can* have virually anytime of day we will
need to include the date so that we can correctly identify the number of
hours. So, with both D3 & E3 with:

D3: 04/07/2008 17:00:00
E3: 04/07/2008 23:30:00

(My Date system)

then in G3 try:

=HVIS(OG(HELTAL(D3)=HELTAL(E3);REST(E3;1)=TID(22; ;));MIN(REST(E3;1);TID(22;;))-MAKS(REST(D3;1);TID(18;;));HVIS(OG(HELTAL(E3)HELT AL(D3);REST(D3;1)<TID(22;;));TID(22;;)-MAKS(REST(D3;1);TID(18;;));0))

and in H3:

=HVIS(OG(HELTAL(D3)=HELTAL(E3);REST(E3;1)TID(22;; ));REST(E3;1)-MAKS(REST(D3;1);TID(22;;));REST((HELTAL(E3)+MIN(RE ST(E3;1);TID(5;;)))-(HELTAL(D3)+MAKS(REST(D3;1);TID(22;;)));1))

In case my translation is wrong my formulas a

In G3:
=IF(AND(INT(D3)=INT(E3),MOD(E3,1)=TIME(22,,)),MAX (MIN(MOD(E3,1),TIME(22,,))-MAX(MOD(D3,1),TIME(18,,)),0),IF(AND(INT(E3)INT(D3 ),MOD(D3,1)<TIME(22,,)),TIME(22,,)-MAX(MOD(D3,1),TIME(18,,)),0))

In H3:
=IF(AND(INT(D3)=INT(E3),MOD(E3,1)TIME(22,,)),MOD( E3,1)-MAX(MOD(D3,1),TIME(22,,)),MOD((INT(E3)+MIN(MOD(E3, 1),TIME(5,,)))-(INT(D3)+MAX(MOD(D3,1),TIME(22,,))),1))

With the above Dates & times and formulas

I get:

F3: 6:30
G3: 4:00
H3: 1:30


And with the same in D3 and 05/07/2008 08:30:00 in E3 I get 07:00 in H3

Does this do what you want?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"H. Nissen" wrote in message
...
Hi again :)

Hello again:)

Unfortunately, there is a bug in the formulas that make a value less than
0:00 (t.ex. 23:30) is that time calculations is not done properly.

Is there any possibility to do this?

Sincerely,

H. Nissen



D3 E3 F3 G3 H3
17:00 23:30 6:30 0:00 12:00

F3 Count total time
G3 Count between 18:00 and 22:00
H3 Count between 22:00 and 05:00







"Sandy Mann" skrev:

In the formula you posted you are still missing the parenthsis after the
E3
in the second MIN() function but you have added an extra one after the
TID(22;0;0))
=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5; 0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3)-MAKS(D3;TID(22;0;0));1))

When you said that you had found that you had found that there was a
parenthsis missing I did not take the trouble to ensure that you had
placed
it in the right place. My apologies, Try:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5; 0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3)-MAKS(D3;TID(22;0;0));1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"H. Nissen" wrote in message
...
Hi Sandy Mann

With the same parameters,

16:00 in D3 and 8:00 in E3 I get this results:

"Total" 16:00
"18:00 - 22:00" 04:00
"22:00 - 05:00" 10:00

It is real strange, that I did not get the same result as you. My
formula
is
this:


=HVIS(OG(D3<E3;MEDIAN(D3;E3;0)=D3);REST(MIN(TID(5; 0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3-MAKS(D3;TID(22;0;0)));1))

Kinds

H. Nissen





"Sandy Mann" skrev:

With 16:00 in D3 and 8:00 in E3 I get:

"Total" 16:00
"18:00 - 22:00" 04:00
"22:00 - 05:00" 07:00

What do you get returned?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"H. Nissen" wrote in message
...

Hi Sandy

I found out, that there was a missing ).

=HVIS(OG(D3<E3;MEDIAN(D3;E3;0)=D3);REST(MIN(TID(5; 0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3-MAKS(D3;TID(22;0;0)));1))

But, the formular still counts all hours after 22:00. Not only the
hours
between 22:00 and 5:00.

But again, thx for your kindly help. :)

Kinds regard


"Sandy Mann" skrev:

The only thing that I can think of is the implied zeros in the
TID()
functions. Try:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5; 0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3-MAKS(D3;TID(22;0;0));1))

Other than that I can't see what else could be wrong but if that
does
not
work then post back nevertheless, some of the clever people around
here
may
be able to suggest something else.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"H. Nissen" wrote in message
...

Hi again Sandy Mann

Formula 18:00 to 22:00 working perfekt, thx very much, it was a
great
help.

The other formula 22:00 to 05:00 did not work. Excell tells me,
that
there
are to few arguments to this function. I had write it in a
danish
lang
excell, so it seems like this:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5; ;);E3)-D3;1);REST(MIN(TID(5;;);E3-MAKS(D3;TID(22;;));1))

OG = And
REST = MOD
TID = TIME
MAKS = MAX.

I cant find any missing parameters, but there must be something
I
dont
write
correct. Can you see what is may be ?

Kinds Regards



"Sandy Mann" skrev:

Replace the 18:00 - 22:00 formula with:

=IF(AND(D3<TIME(22,,),E3<D3),MOD(TIME(22,,)-MAX(TIME(18,,),D3),1),0)

Replace the 22:00 - 05:00 formula with:

=IF(AND(D3<E3,MEDIAN(D3,E3,0)=D3),MOD(MIN(TIME(5,, ),E3)-D3,1),MOD(MIN(TIME(5,,),E3)-MAX(D3,TIME(22,,)),1))

Sorry for misunderstanding your requirements.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"H. Nissen" wrote in
message
...
Hi Sandy Mann

Thx for your answer, it was very helpfull, but there is a
small
problems
yet. The function counts all hours before 22 o'clock and all
hours
after.

It must only count the hours between 18:00 and 22:00 and
again
the
hours
between 22:00 and 5:00.

Ex: from 16:00 to 8:00 there are 16 hours, and 4 hours
between
18:00
to
22:00 and again 7 hours between 22:00 to 8:00.