Don't count 0:00h as 24h

[Micos3]

Hi, i have a table in which counts the hours that are in a period that i
define, i had a precious help, that have gived me this formula:

=MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
A B C D M N
1 18:00 21:00 Ponta 1:00 20:00 0:00
2 21:00 0:00 Cheias 3:00

And it works, except if i put 0:00 in N column, final term. The problem is
that in column D2 it doesn't appear 3:00 it appears 0:00, because he can't
recognize 0:00 as 24:00, ando so returns 0:00. The format is h:mm.

Anyone knows how to solve this problem?
tks

[George Nicholson]

Change the Cell Formating to [h]:mm, then hours won't "roll over"

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Micos3" <[email protected]> wrote in message
news:[email protected]...
> Hi, i have a table in which counts the hours that are in a period that i
> define, i had a precious help, that have gived me this formula:
>
> =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> A B C D M N
> 1 18:00 21:00 Ponta 1:00 20:00 0:00
> 2 21:00 0:00 Cheias 3:00
>
> And it works, except if i put 0:00 in N column, final term. The problem is
> that in column D2 it doesn't appear 3:00 it appears 0:00, because he can't
> recognize 0:00 as 24:00, ando so returns 0:00. The format is h:mm.
>
> Anyone knows how to solve this problem?
> tks

[Micos3]

It is formatted as h:mm.

The problem is in second row.
It works just fine if i put 23:59 in where is 0:00, it counts 2:59.
But as it is right now it counts 0:00, because he reads like in formula
"minimum of 0:00 and 0:00" so it is 0:00, but it should be 24:00, and the
maximum of 21:00 or 20:00, taht he reads 21.
Now the min=0 tha max=21; so it gives 0-21=-21
once the formula is max of(-21;0) it gives back 0:00:00

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(A1;1);$M$1);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.


"George Nicholson" escreveu:

> Change the Cell Formating to [h]:mm, then hours won't "roll over"
>
> HTH,
> --
> George Nicholson
>
> Remove 'Junk' from return address.
>
>
> "Micos3" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, i have a table in which counts the hours that are in a period that i
> > define, i had a precious help, that have gived me this formula:
> >
> > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
> > A B C D M N
> > 1 18:00 21:00 Ponta 1:00 20:00 0:00
> > 2 21:00 0:00 Cheias 3:00
> >
> > And it works, except if i put 0:00 in N column, final term. The problem is
> > that in column D2 it doesn't appear 3:00 it appears 0:00, because he can't
> > recognize 0:00 as 24:00, ando so returns 0:00. The format is h:mm.
> >
> > Anyone knows how to solve this problem?
> > tks
>
>
>

[George Nicholson]

If it is formated as h:mm it will never read 24:00. 23:59 is the highest it
will go before it "rolls over" to 1 day, 0 hours, 0 minutes, i.e., 0:00.

Format of [h]:mm will display 24:00

--
George Nicholson

Remove 'Junk' from return address.


"Micos3" <[email protected]> wrote in message
news:[email protected]...
> It is formatted as h:mm.
>
> The problem is in second row.
> It works just fine if i put 23:59 in where is 0:00, it counts 2:59.
> But as it is right now it counts 0:00, because he reads like in formula
> "minimum of 0:00 and 0:00" so it is 0:00, but it should be 24:00, and the
> maximum of 21:00 or 20:00, taht he reads 21.
> Now the min=0 tha max=21; so it gives 0-21=-21
> once the formula is max of(-21;0) it gives back 0:00:00
>
> 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(A1;1);$M$1);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.
>
>
> "George Nicholson" escreveu:
>
>> Change the Cell Formating to [h]:mm, then hours won't "roll over"
>>
>> HTH,
>> --
>> George Nicholson
>>
>> Remove 'Junk' from return address.
>>
>>
>> "Micos3" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi, i have a table in which counts the hours that are in a period that
>> > i
>> > define, i had a precious help, that have gived me this formula:
>> >
>> > =MAX(MIN(MOD(B1,1),$N$1)-MAX(MOD(A1,1),$M$1),0)
>> > A B C D M
>> > N
>> > 1 18:00 21:00 Ponta 1:00 20:00 0:00
>> > 2 21:00 0:00 Cheias 3:00
>> >
>> > And it works, except if i put 0:00 in N column, final term. The problem
>> > is
>> > that in column D2 it doesn't appear 3:00 it appears 0:00, because he
>> > can't
>> > recognize 0:00 as 24:00, ando so returns 0:00. The format is h:mm.
>> >
>> > Anyone knows how to solve this problem?
>> > tks
>>
>>
>>