Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Don't count 0:00h as 24h
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions |