domingo, 28 de julho de 2013

OUTRO EXEMPLO DE CÁLCULO DE HORAS NEGATIVAS (Another Example for Calculating Negative Hours)

Outra questão que me foi colocada recentemente relacionava-se com o cálculo de atrasos numa escala de produção. Neste caso específico, o atraso implicava que o resultado fosse negativo. E o Excel não reconhece directamente valores negativos quando se trata cálculo com unidades de tempo.
Another question I was asked recently was related to the calculation of delays on a production scale. In this particular case, the delay meant that the result was negative. And Excel does not directly recognize negative values ​​when it comes to calculation units of time.

A solução apresentada foi a seguinte:
My solution was as follows:


Fórmula da Célula E6 e seguintes:
= SE(D6>C6;RESTO(D6-C6;1);RESTO(C6-D6;1))
Formula in E6 and below:
= IF(D6>C6,MOD(D6-C6,1);MOD(C6-D6,1))
As células a vermelho realçam os casos em que o resultado é negativo e, por isso, existe um atraso  na escala.
Para que essas células apareçam com fundo vermelho, recorre-se à formatação condicional:
The red cells to enhance the cases in which the result is negative and, therefore, there is a delay in scale.

For these cells appear with red background, makes use of the conditional formatting:
Formatação Condicional / Conditional Format

sábado, 27 de julho de 2013

CALCULAR HORAS ENTRE DUAS DATAS COM UMA CONDIÇÃO (Calculate Hours Between Two Dates with a Condition)

O cálculo com unidades de tempo (horas, dias, etc.) tem sido um dos temas mais recorrentes aqui no blog.

Há alguns dias atrás, puseram-me uma questão interessante. Tratava-se de calcular o número de horas entre duas datas, subtraindo um determinado conjunto de horas (16) a cada dia do intervalo, caso esse intervalo fosse superior a 24 horas.

Apresentei duas soluções para chegar ao mesmo resultado:

Na primeira, utilizei a função SE para criar um conjunto de condições (Tabela 1).
Na segunda, utilizei as funções DATADIF (que já foi objecto de um post - http://exceleoutrascoisas.blogspot.pt/search/label/DATADIF) e TEMPO.


Fórmulas da Tabela 1:
D4 =SE((C4-B4)*24<16;(C4-B4)*24;SE(E((C4-B4)*24>=16;(C4-B4)*24<=24);(C4-B4)*24-16;SE((C4-B4)*24>24;(C4-B4)*24-(ARRED((C4-B4)*24/24;0)*16)))))
- A formatação da célula é 0,00
E4 =(SE((C4-B4)*24<16;(C4-B4)*24;SE(E((C4-B4)*24>=16;(C4-B4)*24<=24);(C4-B4)*24-16;SE((C4-B4)*24>24;(C4-B4)*24-(ARRED((C4-B4)*24/24;0)*16)))))/24 
- A formatação da célula é [hh]:mm

Fórmula da Tabela 2:
D12 =C12-B12-DATADIF(B12;C12;"d")*TEMPO(16;0;0)
- A formatação da célula é [hh]:mm

Time units calculation (hours, days, etc.) has been one of the most frequently issues here in the blog.

A few days ago, someone put me an interesting question. It was abour calculating the number of hours between two dates by subtracting a set of hours (16) in each day in the range if that range was longer than 24 hours.

I have presented two solutions to reach the same result:

In the first one, I used the IF function to create a set of conditions (Table 1).
In the second, I used DATEDIF function (which has been the subject of a post - http://exceleoutrascoisas.blogspot.pt/search/label/DATADIF) and TIME function.



Formulas in Table 1:
D4 =IF((C4-B4)*24<16,(C4-B4)*24,IF(AND((C4-B4)*24>=16,(C4-B4)*24<=24),(C4-B4)*24-16,IF((C4-B4)*24>24,(C4-B4)*24-(ROUND((C4-B4)*24/24,0)*16)))))
- Cell format is 0.00
E4 =(IF((C4-B4)*24<16,(C4-B4)*24,IF(AND((C4-B4)*24>=16,(C4-B4)*24<=24),(C4-B4)*24-16,IF((C4-B4)*24>24,(C4-B4)*24-(ROUND((C4-B4)*24/24,0)*16)))))/24 
- Cell format is [hh]: mm

Formula in Table 2:
D12 = C12-B12-DATEDIF (B12, C12, "d") * TIME (16, 0, 0)
- Cell format is [hh]: mm