Count time table

[Guest]

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($A1>N1;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;B1>M1);B1-M1;IF(B1<M1);0))))

Understand what i a'm trying to do?
tks

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[Bob Phillips]

give me an example that returns ####### , and one of the 24:00

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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:3
###############################################################################################################################################################################################################################################################
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:

 

[Guest]

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:

 

[Guest]

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:

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[Guest]

U don't mind that i put another post just because of the question of 24h - 0h ?
tks

"Bob Phillips" escreveu:

 

[Guest]

I want to thank you, for all the help gived.

As we say, "Obrigado"

"Bob Phillips" escreveu:

 

[Bob Phillips]

de nada, pero la pelabra diberia estar obligado, ¿no cierto? <G>

 

[Guest]

Espiero que me comprendas, pero la pelabra está muy bien
Lo que ay dicho es Português, no eres Español

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:

 

[Bob Phillips]

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)

 

[Guest]

But i hope u know Portugal cos i find many ppl in web that thought that we
are a spanish provence, i hope not

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:

 

[Sandy Mann]

Using Bob's 1st formula as an example:

=MAX(MIN(B1,$N$1)-MAX(A1,$M$1),0)



You could convert the times to numbers and then back to times after the
calculation:

=MAX(MIN(IF(B1=0,24,B1*24),IF(N1=0,24,$N$1*24))-MAX(IF(A1=0,24,A1*24),IF(M1=0,24,$M$1*24)),0)/24

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[Guest]

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: