|
Count time table
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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
give me an example that returns ####### , and one of the 24:00
-- HTH RP (remove nothere from the email address if mailing direct) "Micos3" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G
"Micos3" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
|
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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($A1N1;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;B1M1);B1-M1;IF(B1<M1);0;))))) Understand what i a'm trying to do? tks |
| All times are GMT +1. The time now is 08:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com