Don't count 0:00h as 24h

[Guest]

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,

 

[Guest]

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.

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.

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.

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