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" wrote in message
...
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" wrote in message
...
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
|