What i want to do is dificult to explain so i'll try making a scheme.

A B C D M N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C the
name i want to other mission, in cell D i count the hours that period M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having trouble
in counting 0:00 (24:00), example of line 1.

=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks

Not exhaustively tested, but try this

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
What i want to do is dificult to explain so i'll try making a scheme.

A B C D M N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C the
name i want to other mission, in cell D i count the hours that period M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having trouble
in counting 0:00 (24:00), example of line 1.


=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M
1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks

Now it detects the count but only some, i dont understand why...
And if i copy that formula to other cell changing to M2:N2 all cells become
####### and.........
It doesn't recognize 24h too because if i put 24: it appears inside that
cell this "01-01-1900 0:00:00"

since i've started to do this program this last error appeared allways but i
thought because of my newbieness, and it is.... but how to solve it? And the
rest?
tks

"Bob Phillips" escreveu:

Not exhaustively tested, but try this

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
What i want to do is dificult to explain so i'll try making a scheme.

A B C D M N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C the
name i want to other mission, in cell D i count the hours that period M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having trouble
in counting 0:00 (24:00), example of line 1.


=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M
1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks

give me an example that returns ####### , and one of the 24:00

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
Now it detects the count but only some, i dont understand why...
And if i copy that formula to other cell changing to M2:N2 all cells
become
####### and.........
It doesn't recognize 24h too because if i put 24: it appears inside that
cell this "01-01-1900 0:00:00"

since i've started to do this program this last error appeared allways but
i
thought because of my newbieness, and it is.... but how to solve it? And
the
rest?
tks

"Bob Phillips" escreveu:

Not exhaustively tested, but try this

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
What i want to do is dificult to explain so i'll try making a scheme.

A B C D M
N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C
the
name i want to other mission, in cell D i count the hours that period
M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having
trouble
in counting 0:00 (24:00), example of line 1.



=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M
1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks

E F G
35 9:30 12:00 Ponta
36 18:30 21:00 Ponta
37 7:00 9:30 Cheias

Entrada SaÃ*da
Turno 1 8:00 16:00
Turno 2 16:00 0:00

2:30
################################################## ################################################## ################################################## ################################################## ################################################## #####
1:30

The fórmula that maked ### that u see is in a cell and is:
=max(min($F36;$N$22)-max(max($E36;$M$22);0)) and gives the upper aspect :O.


The next doubt, is better to say step by step, what hapenned:
I tested right now the cell that contains 0:00:00, the " means start" and
Enter", ok?
If i put in that cell "0:" it writes like "0:00:00", but if i write "24:"
or"24"
it writes 0:00 in cell but if u go to cell it is exactly like "01-01-1900
0:00:00", and the cell in program that needs that values doesn't recognize
anymore.

Weird no?

"Bob Phillips" escreveu:

give me an example that returns ####### , and one of the 24:00

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
Now it detects the count but only some, i dont understand why...
And if i copy that formula to other cell changing to M2:N2 all cells
become
####### and.........
It doesn't recognize 24h too because if i put 24: it appears inside that
cell this "01-01-1900 0:00:00"

since i've started to do this program this last error appeared allways but
i
thought because of my newbieness, and it is.... but how to solve it? And
the
rest?
tks

"Bob Phillips" escreveu:

Not exhaustively tested, but try this

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
What i want to do is dificult to explain so i'll try making a scheme.

A B C D M
N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C
the
name i want to other mission, in cell D i count the hours that period
M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having
trouble
in counting 0:00 (24:00), example of line 1.



=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M
1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks

I noted a detail, when i write "24" it appears diferent "24-01-1900 0:00:00";
if i write "24:", it appears "01-01-1900 0:00:00";
In the 1º i'm changing the date?!?!?

I notest other detail, in previous post of mine:
i had 0:00 in the N22 and 8:00 in M22, they all ###################.



"Bob Phillips" escreveu:

give me an example that returns ####### , and one of the 24:00

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
Now it detects the count but only some, i dont understand why...
And if i copy that formula to other cell changing to M2:N2 all cells
become
####### and.........
It doesn't recognize 24h too because if i put 24: it appears inside that
cell this "01-01-1900 0:00:00"

since i've started to do this program this last error appeared allways but
i
thought because of my newbieness, and it is.... but how to solve it? And
the
rest?
tks

"Bob Phillips" escreveu:

Not exhaustively tested, but try this

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
What i want to do is dificult to explain so i'll try making a scheme.

A B C D M
N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C
the
name i want to other mission, in cell D i count the hours that period
M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having
trouble
in counting 0:00 (24:00), example of line 1.



=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M
1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks

The ###### was solved, cos was one parentisis that was blowing all.
The problem of 24 or 0h i don't see solution for it.

tks

"Micos3" escreveu:

I noted a detail, when i write "24" it appears diferent "24-01-1900 0:00:00";
if i write "24:", it appears "01-01-1900 0:00:00";
In the 1º i'm changing the date?!?!?

I notest other detail, in previous post of mine:
i had 0:00 in the N22 and 8:00 in M22, they all ###################.



"Bob Phillips" escreveu:

give me an example that returns ####### , and one of the 24:00

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
Now it detects the count but only some, i dont understand why...
And if i copy that formula to other cell changing to M2:N2 all cells
become
####### and.........
It doesn't recognize 24h too because if i put 24: it appears inside that
cell this "01-01-1900 0:00:00"

since i've started to do this program this last error appeared allways but
i
thought because of my newbieness, and it is.... but how to solve it? And
the
rest?
tks

"Bob Phillips" escreveu:

Not exhaustively tested, but try this

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Micos3" wrote in message
...
What i want to do is dificult to explain so i'll try making a scheme.

A B C D M
N
1 9:30 12:00 Ponta 2:30 8:00 16:00
2 21:00 0:00 Cheias 0:00
3 07:00 09:30 Cheias 1:30
4 0:00 07:00 Vazias 0:00

I table ABC i count many periods of the day in wich i call in cell C
the
name i want to other mission, in cell D i count the hours that period
M:N
shows that are in period A:B.
I've made this formula but isn't working well, and also i'm having
trouble
in counting 0:00 (24:00), example of line 1.



=IF($A1N1;0;IF(AND($A1$M1;$N$1=$B1);B1-A1;IF(AND($A1$M1;$N$1=<$B1;$A1$M
1);N1-A1;IF(AND($A1<$M1;$N$1=$B1;B1M1);B1-M1;IF(B1<M1);0;)))))

Understand what i a'm trying to do?
tks