Excel 2010: Horário de verão - calcula o número de horas entre registros de data e hora para qualquer dia

Question

Eu tenho uma folha de verificação que cruza vários pontos de medição de água, lendo as taxas médias de cerca de trinta metros diferentes de outro sistema. Eu automatizei esta folha de verificação para que um script VBA possa executar a folha várias vezes em um determinado período (geralmente um mês por vez) e extrair os resultados para outra página.

O Reino Unido teve seu Daylight Savings ontem (domingo, 25 de outubro de 2015) e isso está gerando problemas para mim. Todas as leituras medidas estão me dando uma 'vazão média horária' e eu tenho multiplicado por 24 para dar uma taxa diária. Mas agora estou consciente de que dois dias por ano, 24 é o multiplicador errado para usar.

Então, dado um Start_Data de 25/10/2015 00:00 e End_Date de 26/10/2015 00:00, como posso obter o Excel para calcular o número correto de horas entre eles, que também trabalhar para todos os outros dias a cada ano? Eu quero ser capaz de executar esta planilha de forma confiável em qualquer período e para cada dia calcular o total diário dependendo se ela tem 23, 24 ou 25 horas. Todas as conversões que tentei até agora retornam a diferença entre os dois timestamps como '1', já que o Excel conta as 25h como um dia.

Edit: Para o registro, o Reino Unido muda nos últimos domingos de março e outubro.

    

Answer 1

Na ausência de um método melhor, agora aplico a seguinte solução:

=Flowrate*(24+IF(AND(EOMONTH(Start_Date,0)-Start_Date<7,WEEKDAY(Start_Date,3)=6),CHOOSE(MONTH(Start_Date),0,0,-1,0,0,0,0,0,0,1,0,0),0))

Como funciona

Em resumo, trabalhando de trás para frente:

CHOOSE(MONTH(Start_Date),0,0,-1,0,0,0,0,0,0,1,0,0)

Para uma determinada Start_Date, verifica qual mês do ano é e retorna um deslocamento de -1 hora se for março e uma hora se outubro.

WEEKDAY(Start_Date,3) = 6

Para uma determinada Start_Date, verifica se a data atual é um domingo e retorna TRUE em caso afirmativo.

EOMONTH(Start_Date,0)-Start_Date < 7

Para uma determinada Start_Date, verifica se há menos de sete dias a partir do final do mês. Retorna TRUE em caso afirmativo.

AND(EOMONTH(),WEEKDAY()=6)

Verifica as duas condições acima. Se é domingo e nos últimos seis dias do mês (ou seja, não mais domingos), retorne TRUE.

IF(AND(),CHOOSE(),0)

Se as condições acima forem todas TRUE, invoque CHOOSE para retornar o deslocamento correto para o último domingo do mês fornecido. Caso contrário, retorne zero. O offset retornado é adicionado a 24 para dar o número de horas no dia dado, que pode então ser multiplicado contra a vazão horária para dar o total do dia.