Count time table

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
>
> Understand what i a'm trying to do?
> tks

[Micos3]

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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))

Understand what i a'm trying to do?
tks

[Micos3]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> >
> > Understand what i a'm trying to do?
> > tks
>
>
>

[Bob Phillips]

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

--

HTH

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


"Micos3" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > >
> > > Understand what i a'm trying to do?
> > > tks
> >
> >
> >

[Micos3]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > >
> > > > Understand what i a'm trying to do?
> > > > tks
> > >
> > >
> > >
>
>
>

[Micos3]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > >
> > > > Understand what i a'm trying to do?
> > > > tks
> > >
> > >
> > >
>
>
>

[Micos3]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > >
> > > > > Understand what i a'm trying to do?
> > > > > tks
> > > >
> > > >
> > > >
> >
> >
> >

[Bob Phillips]

Micos3

Try this

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

shoule negate the effects of 24:00.

--

HTH

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


"Micos3" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > >
> > > > > > Understand what i a'm trying to do?
> > > > > > tks
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

[Micos3]

It works if instead of using 0:00 put 23:59h

but neither 24: or 0:00 works, he just can't recognize 0:00 as 24, so if i
put a period as 16:00 - 0:00, i guess he reads as an impossible period, so it
takes 0:00 in the cells where i put those formulas.
If i put period 0:00 - 16:00 it works just fine, but contrary doesn't.

It's hard to understand as a minor detail,as midnight, is mining all the
work.....
tks

:"Bob Phillips" escreveu:

> Micos3
>
> Try this
>
> =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
>
> shoule negate the effects of 24:00.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > >
> > > > > > > Understand what i a'm trying to do?
> > > > > > > tks
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

[Micos3]

U don't mind that i put another post just because of the question of 24h - 0h ?
tks

"Bob Phillips" escreveu:

> Micos3
>
> Try this
>
> =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
>
> shoule negate the effects of 24:00.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > >
> > > > > > > Understand what i a'm trying to do?
> > > > > > > tks
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

[Micos3]

I want to thank you, for all the help gived.

As we say, "Obrigado"

"Bob Phillips" escreveu:

> Micos3
>
> Try this
>
> =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
>
> shoule negate the effects of 24:00.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > >
> > > > > > > Understand what i a'm trying to do?
> > > > > > > tks
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

[Bob Phillips]

de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>



"Micos3" <[email protected]> wrote in message
news:[email protected]...
> I want to thank you, for all the help gived.
>
> As we say, "Obrigado"
>
> "Bob Phillips" escreveu:
>
> > Micos3
> >
> > Try this
> >
> > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> >
> > shoule negate the effects of 24:00.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Micos3" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > >
> > > > > > > > Understand what i a'm trying to do?
> > > > > > > > tks
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >

[Micos3]

Espiero que me comprendas, pero la pelabra está muy bien
Lo que ay dicho es Português, no eres Español :D

Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........

Mi espanhol isn't very good (neither my english but....) , so i'll change it
to other language, at least i know u undearstand me.
The formula u gaved me, can be translated to instead of use hours to use
numbers?
The biggest problem is the minuts, can it be solved?
Muchas Gracias


"Bob Phillips" escreveu:

> de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
>
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > I want to thank you, for all the help gived.
> >
> > As we say, "Obrigado"
> >
> > "Bob Phillips" escreveu:
> >
> > > Micos3
> > >
> > > Try this
> > >
> > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > >
> > > shoule negate the effects of 24:00.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Micos3" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > > >
> > > > > > > > > Understand what i a'm trying to do?
> > > > > > > > > tks
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Micos,

LOL. I thought I saw that you were in California so I assumed it was
Spanish.

Can you explain a bit more, giving some examples of the data that causes a
problem?

--

HTH

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


"Micos3" <[email protected]> wrote in message
news:[email protected]...
> Espiero que me comprendas, pero la pelabra está muy bien
> Lo que ay dicho es Português, no eres Español :D
>
> Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........
>
> Mi espanhol isn't very good (neither my english but....) , so i'll change
it
> to other language, at least i know u undearstand me.
> The formula u gaved me, can be translated to instead of use hours to use
> numbers?
> The biggest problem is the minuts, can it be solved?
> Muchas Gracias
>
>
> "Bob Phillips" escreveu:
>
> > de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
> >
> >
> >
> > "Micos3" <[email protected]> wrote in message
> > news:[email protected]...
> > > I want to thank you, for all the help gived.
> > >
> > > As we say, "Obrigado"
> > >
> > > "Bob Phillips" escreveu:
> > >
> > > > Micos3
> > > >
> > > > Try this
> > > >
> > > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > > >
> > > > shoule negate the effects of 24:00.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Micos3" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > > > >
> > > > > > > > > > Understand what i a'm trying to do?
> > > > > > > > > > tks
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Micos3]

But i hope u know Portugal cos i find many ppl in web that thought that we
are a spanish provence, i hope not :D

What i said it was to, instead of try to solve the impossible unsolved
question of the 24h problem.

I've putted a new post "Don't count 0:00h as 24h" in which i develope more
about the problem.

One way i'm triyng to solve the problem is to do an "if" in the cell that
makes the reading of 0:00 and changes it to 24 only in formula, something
like this:
=MAX(MIN(MOD(if(B1=0:00:00;24:00:00;b1);1);IF($N$1=0:00:00;24:00:00;$N$23))-MAX(MOD(E40;1);$M$23);0)

Understand what i'm triyng to do?
It could be used an hide cell in which it could read the diference if the
cell as 0:00 in final term of the period.

Obrigado

"Bob Phillips" escreveu:

> Micos,
>
> LOL. I thought I saw that you were in California so I assumed it was
> Spanish.
>
> Can you explain a bit more, giving some examples of the data that causes a
> problem?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > Espiero que me comprendas, pero la pelabra está muy bien
> > Lo que ay dicho es Português, no eres Español :D
> >
> > Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........
> >
> > Mi espanhol isn't very good (neither my english but....) , so i'll change
> it
> > to other language, at least i know u undearstand me.
> > The formula u gaved me, can be translated to instead of use hours to use
> > numbers?
> > The biggest problem is the minuts, can it be solved?
> > Muchas Gracias
> >
> >
> > "Bob Phillips" escreveu:
> >
> > > de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
> > >
> > >
> > >
> > > "Micos3" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I want to thank you, for all the help gived.
> > > >
> > > > As we say, "Obrigado"
> > > >
> > > > "Bob Phillips" escreveu:
> > > >
> > > > > Micos3
> > > > >
> > > > > Try this
> > > > >
> > > > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > > > >
> > > > > shoule negate the effects of 24:00.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Micos3" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > > > > >
> > > > > > > > > > > Understand what i a'm trying to do?
> > > > > > > > > > > tks
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Sandy Mann]

Using Bob's 1st formula as an example:

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



You could convert the times to numbers and then back to times after the
calculation:

=MAX(MIN(IF(B1=0,24,B1*24),IF(N1=0,24,$N$1*24))-MAX(IF(A1=0,24,A1*24),IF(M1=0,24,$M$1*24)),0)/24

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

"Micos3" <[email protected]> wrote in message
news:[email protected]...
> But i hope u know Portugal cos i find many ppl in web that thought that
> we
> are a spanish provence, i hope not :D
>
> What i said it was to, instead of try to solve the impossible unsolved
> question of the 24h problem.
>
> I've putted a new post "Don't count 0:00h as 24h" in which i develope more
> about the problem.
>
> One way i'm triyng to solve the problem is to do an "if" in the cell that
> makes the reading of 0:00 and changes it to 24 only in formula, something
> like this:
> =MAX(MIN(MOD(if(B1=0:00:00;24:00:00;b1);1);IF($N$1=0:00:00;24:00:00;$N$23))-MAX(MOD(E40;1);$M$23);0)
>
> Understand what i'm triyng to do?
> It could be used an hide cell in which it could read the diference if the
> cell as 0:00 in final term of the period.
>
> Obrigado
>
> "Bob Phillips" escreveu:
>
>> Micos,
>>
>> LOL. I thought I saw that you were in California so I assumed it was
>> Spanish.
>>
>> Can you explain a bit more, giving some examples of the data that causes
>> a
>> problem?
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Micos3" <[email protected]> wrote in message
>> news:[email protected]...
>> > Espiero que me comprendas, pero la pelabra está muy bien
>> > Lo que ay dicho es Português, no eres Español :D
>> >
>> > Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........
>> >
>> > Mi espanhol isn't very good (neither my english but....) , so i'll
>> > change
>> it
>> > to other language, at least i know u undearstand me.
>> > The formula u gaved me, can be translated to instead of use hours to
>> > use
>> > numbers?
>> > The biggest problem is the minuts, can it be solved?
>> > Muchas Gracias
>> >
>> >
>> > "Bob Phillips" escreveu:
>> >
>> > > de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
>> > >
>> > >
>> > >
>> > > "Micos3" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > I want to thank you, for all the help gived.
>> > > >
>> > > > As we say, "Obrigado"
>> > > >
>> > > > "Bob Phillips" escreveu:
>> > > >
>> > > > > Micos3
>> > > > >
>> > > > > Try this
>> > > > >
>> > > > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
>> > > > >
>> > > > > shoule negate the effects of 24:00.
>> > > > >
>> > > > > --
>> > > > >
>> > > > > HTH
>> > > > >
>> > > > > RP
>> > > > > (remove nothere from the email address if mailing direct)
>> > > > >
>> > > > >
>> > > > > "Micos3" <[email protected]> wrote in message
>> > > > > news:[email protected]...
>> > > > > > 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" <[email protected]> wrote in
>> > > > > > > > message
>> > > > > > > > news:[email protected]...
>> > > > > > > > > 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" <[email protected]> wrote in
>> message
>> > > > > > > > > > news:[email protected]...
>> > > > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
>> > > > > > > > > > >
>> > > > > > > > > > > Understand what i a'm trying to do?
>> > > > > > > > > > > tks
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
>>
>>

[Bob Phillips]

I may not recognise the difference between Spanish and Portuguese, but of
course I know Portugal, I am English not American! We still remember
Portugal doing us in the 2004 European Championship, especially the Rui
Costa goal. And of course, we all know Luis Figo, he scored a great goal
against us and ran the game.

Anyway, the problem. With the formula I gave you I get these results

24:00:00 14:00 06:00
00:00 14:00 06:00
12:00 00:00 00:00
12:00 24:00:00 00:00


what do you want in these circumstances?


--

HTH

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


"Micos3" <[email protected]> wrote in message
news:[email protected]...
> But i hope u know Portugal cos i find many ppl in web that thought that
we
> are a spanish provence, i hope not :D
>
> What i said it was to, instead of try to solve the impossible unsolved
> question of the 24h problem.
>
> I've putted a new post "Don't count 0:00h as 24h" in which i develope more
> about the problem.
>
> One way i'm triyng to solve the problem is to do an "if" in the cell that
> makes the reading of 0:00 and changes it to 24 only in formula, something
> like this:
>
=MAX(MIN(MOD(if(B1=0:00:00;24:00:00;b1);1);IF($N$1=0:00:00;24:00:00;$N$23))-
MAX(MOD(E40;1);$M$23);0)
>
> Understand what i'm triyng to do?
> It could be used an hide cell in which it could read the diference if the
> cell as 0:00 in final term of the period.
>
> Obrigado
>
> "Bob Phillips" escreveu:
>
> > Micos,
> >
> > LOL. I thought I saw that you were in California so I assumed it was
> > Spanish.
> >
> > Can you explain a bit more, giving some examples of the data that causes
a
> > problem?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Micos3" <[email protected]> wrote in message
> > news:[email protected]...
> > > Espiero que me comprendas, pero la pelabra está muy bien
> > > Lo que ay dicho es Português, no eres Español :D
> > >
> > > Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........
> > >
> > > Mi espanhol isn't very good (neither my english but....) , so i'll
change
> > it
> > > to other language, at least i know u undearstand me.
> > > The formula u gaved me, can be translated to instead of use hours to
use
> > > numbers?
> > > The biggest problem is the minuts, can it be solved?
> > > Muchas Gracias
> > >
> > >
> > > "Bob Phillips" escreveu:
> > >
> > > > de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
> > > >
> > > >
> > > >
> > > > "Micos3" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I want to thank you, for all the help gived.
> > > > >
> > > > > As we say, "Obrigado"
> > > > >
> > > > > "Bob Phillips" escreveu:
> > > > >
> > > > > > Micos3
> > > > > >
> > > > > > Try this
> > > > > >
> > > > > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > > > > >
> > > > > > shoule negate the effects of 24:00.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > RP
> > > > > > (remove nothere from the email address if mailing direct)
> > > > > >
> > > > > >
> > > > > > "Micos3" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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" <[email protected]> wrote in
> > message
> > > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > > > > > >
> > > > > > > > > > > > Understand what i a'm trying to do?
> > > > > > > > > > > > tks
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Micos3]

I don't understand what your example means, sorry.
So i leave here all table i want to make, for this legal period:

Legal Period Shift 1 Shift
2 Shift 3 Total
A 9:30 12:00 Ponta 2:30 0:00 0:00 2:30
B 18:30 21:00 Ponta 0:00 2:30 0:00 2:30
C 7:00 9:30 Cheias 1:30 0:00 1:00 2:30
D 12:00 18:30 Cheias 4:00 2:30 0:00 6:30
E 21:00 0:00 Cheias 0:00 3:00 0:00 3:00
F 0:00 7:00 Vazio 0:00 0:00 7:00 7:00

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

This could be done manually, the problem is that i want to vary with other
legal periods, i want to modify the times of shifts, and other things too.

So your formula works in shift 1, and in 3. The 2º shift, it doesn't work in
line E, it counts 0:00, but if i change shift 2 to end at 23:59, it
automatically in line E changes to 2:59.

Sorry to be so exhaustive and to bother u so much, but because of a
completely minor problem all work doesn't work......



"Bob Phillips" escreveu:

> I may not recognise the difference between Spanish and Portuguese, but of
> course I know Portugal, I am English not American! We still remember
> Portugal doing us in the 2004 European Championship, especially the Rui
> Costa goal. And of course, we all know Luis Figo, he scored a great goal
> against us and ran the game.
>
> Anyway, the problem. With the formula I gave you I get these results
>
> 24:00:00 14:00 06:00
> 00:00 14:00 06:00
> 12:00 00:00 00:00
> 12:00 24:00:00 00:00
>
>
> what do you want in these circumstances?
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > But i hope u know Portugal cos i find many ppl in web that thought that
> we
> > are a spanish provence, i hope not :D
> >
> > What i said it was to, instead of try to solve the impossible unsolved
> > question of the 24h problem.
> >
> > I've putted a new post "Don't count 0:00h as 24h" in which i develope more
> > about the problem.
> >
> > One way i'm triyng to solve the problem is to do an "if" in the cell that
> > makes the reading of 0:00 and changes it to 24 only in formula, something
> > like this:
> >
> =MAX(MIN(MOD(if(B1=0:00:00;24:00:00;b1);1);IF($N$1=0:00:00;24:00:00;$N$23))-
> MAX(MOD(E40;1);$M$23);0)
> >
> > Understand what i'm triyng to do?
> > It could be used an hide cell in which it could read the diference if the
> > cell as 0:00 in final term of the period.
> >
> > Obrigado
> >
> > "Bob Phillips" escreveu:
> >
> > > Micos,
> > >
> > > LOL. I thought I saw that you were in California so I assumed it was
> > > Spanish.
> > >
> > > Can you explain a bit more, giving some examples of the data that causes
> a
> > > problem?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Micos3" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Espiero que me comprendas, pero la pelabra está muy bien
> > > > Lo que ay dicho es Português, no eres Español :D
> > > >
> > > > Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........
> > > >
> > > > Mi espanhol isn't very good (neither my english but....) , so i'll
> change
> > > it
> > > > to other language, at least i know u undearstand me.
> > > > The formula u gaved me, can be translated to instead of use hours to
> use
> > > > numbers?
> > > > The biggest problem is the minuts, can it be solved?
> > > > Muchas Gracias
> > > >
> > > >
> > > > "Bob Phillips" escreveu:
> > > >
> > > > > de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
> > > > >
> > > > >
> > > > >
> > > > > "Micos3" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I want to thank you, for all the help gived.
> > > > > >
> > > > > > As we say, "Obrigado"
> > > > > >
> > > > > > "Bob Phillips" escreveu:
> > > > > >
> > > > > > > Micos3
> > > > > > >
> > > > > > > Try this
> > > > > > >
> > > > > > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > > > > > >
> > > > > > > shoule negate the effects of 24:00.
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "Micos3" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > > > > > > >
> > > > > > > > > > > > > Understand what i a'm trying to do?
> > > > > > > > > > > > > tks
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Micos3]

I have to apologise to you, cos u gave the right awnser from the beggining
and i didn't understand u well. U posted to put the format [h]:mm, but that
format is not available, so i only put h:mm.
Now, for sugestion of George Nicholson i said to myself that it wasn't
possible, but i went to custom and so i finally think a litle and try to
format the h:mm to [h]:mm and it worked. ur 1º formula works just fine.
I was to ask u if i want to pass hours to number but i try to *24 and
automattically gives me a number that correspondes the hour.
So i have to thank u a lot for all the pacience that u had with me.

Muito Obrigado!!!

"Bob Phillips" escreveu:

> I may not recognise the difference between Spanish and Portuguese, but of
> course I know Portugal, I am English not American! We still remember
> Portugal doing us in the 2004 European Championship, especially the Rui
> Costa goal. And of course, we all know Luis Figo, he scored a great goal
> against us and ran the game.
>
> Anyway, the problem. With the formula I gave you I get these results
>
> 24:00:00 14:00 06:00
> 00:00 14:00 06:00
> 12:00 00:00 00:00
> 12:00 24:00:00 00:00
>
>
> what do you want in these circumstances?
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > But i hope u know Portugal cos i find many ppl in web that thought that
> we
> > are a spanish provence, i hope not :D
> >
> > What i said it was to, instead of try to solve the impossible unsolved
> > question of the 24h problem.
> >
> > I've putted a new post "Don't count 0:00h as 24h" in which i develope more
> > about the problem.
> >
> > One way i'm triyng to solve the problem is to do an "if" in the cell that
> > makes the reading of 0:00 and changes it to 24 only in formula, something
> > like this:
> >
> =MAX(MIN(MOD(if(B1=0:00:00;24:00:00;b1);1);IF($N$1=0:00:00;24:00:00;$N$23))-
> MAX(MOD(E40;1);$M$23);0)
> >
> > Understand what i'm triyng to do?
> > It could be used an hide cell in which it could read the diference if the
> > cell as 0:00 in final term of the period.
> >
> > Obrigado
> >
> > "Bob Phillips" escreveu:
> >
> > > Micos,
> > >
> > > LOL. I thought I saw that you were in California so I assumed it was
> > > Spanish.
> > >
> > > Can you explain a bit more, giving some examples of the data that causes
> a
> > > problem?
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Micos3" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Espiero que me comprendas, pero la pelabra está muy bien
> > > > Lo que ay dicho es Português, no eres Español :D
> > > >
> > > > Figo, Mourinho, Rui Costa, Cristiano Ronaldo, Mariza........
> > > >
> > > > Mi espanhol isn't very good (neither my english but....) , so i'll
> change
> > > it
> > > > to other language, at least i know u undearstand me.
> > > > The formula u gaved me, can be translated to instead of use hours to
> use
> > > > numbers?
> > > > The biggest problem is the minuts, can it be solved?
> > > > Muchas Gracias
> > > >
> > > >
> > > > "Bob Phillips" escreveu:
> > > >
> > > > > de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>
> > > > >
> > > > >
> > > > >
> > > > > "Micos3" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I want to thank you, for all the help gived.
> > > > > >
> > > > > > As we say, "Obrigado"
> > > > > >
> > > > > > "Bob Phillips" escreveu:
> > > > > >
> > > > > > > Micos3
> > > > > > >
> > > > > > > Try this
> > > > > > >
> > > > > > > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > > > > > >
> > > > > > > shoule negate the effects of 24:00.
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > RP
> > > > > > > (remove nothere from the email address if mailing direct)
> > > > > > >
> > > > > > >
> > > > > > > "Micos3" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > > 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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))
> > > > > > > > > > > > >
> > > > > > > > > > > > > Understand what i a'm trying to do?
> > > > > > > > > > > > > tks
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>