Estou preparando um relatório de estatísticas de uso para um sistema de reserva de sala de estudo em grupo que usamos na biblioteca em que trabalho. Eu exportei todas as reservas do MySQL no Excel para o ano passado e estou tentando chegar a um valor médio para todas as reservas agendadas.
A fórmula que estou usando para calcular as diferenças entre a hora de início e a hora de término de cada reserva, (=TEXT[end_time]-[start_time],"h:mm") , funciona muito bem, mas tenho um problema. Os horários finais das reservas são listados como 00:29 quando um termina na meia hora e 00:59 quando termina na hora.
Eu tenho algumas milhares de linhas na minha planilha e não quero usar o Find & Substitua para alterar esses valores para 00:30 e 00:00. Existe uma maneira de alterar a minha fórmula para arredondar a hora final ou, possivelmente, adicionar um minuto à hora final da fórmula.
Edite com base no comentário de Iszi:
end_time - start_time + TIME(0,1,0)
Documentação para a função TIME ()
Resposta original:
Você pode adicionar 1 minuto no seu cálculo end_time - start_time percebendo que no Excel 1 dia é igual ao inteiro 1, então sua fórmula seria algo como:
end_time - start_time + 1/24/60
(dividindo por 24 horas e 60 minutos).
Você poderia fazer isso sem matemática. Para hora de início em A1 e hora de término em B2:
=SUBSTITUTE(SUBSTITUTE(TEXT(B1-A1,"h:mm"),":29",":30"),":59",":00")
SUBSTITUTE é uma função de substituição de texto bastante simples. Requer três parâmetros:
SUBSTITUTE somente substitui o texto quando há uma correspondência com o parâmetro 2, e então somente substitui o texto correspondente - o resto da string é deixado em paz e strings que não combinam nada são deixados intactos.
Então, começamos acima com o cálculo do tempo original da sua pergunta. Isso é passado para um SUBSTITUTO que verifica ": 29" e substitui por ": 30". (Se ": 29" não for encontrado, não faz nada.) Então a string resultante é passada para o SUBSTITUTE mais externo que verifica ": 59" e substitui por ": 00" conforme necessário.
Se o seu horário de início e de término estiverem exatamente nas horas e meia hora, suas fórmulas não devem retornar durações de 29 ou 59 minutos. Espero que seja possível que a exportação do banco de dados também inclua alguns segundos nos horários. Podemos eliminar totalmente os segundos e calcular as durações que pressupõem que o início / fim estão em zero segundo em cada minuto, assim:
(o início está em A1. O final está em B1)
=TEXT(TIME(HOUR(B1),MINUTE(B1),0)-TIME(HOUR(A1),MINUTE(A1),0),"h:mm")
TIME é uma função que irá gerar um "número de série" do Excel usando horas, minutos e segundos, e depois exibi-lo em um formato de hora padrão. "Números de série" são como o Excel armazena valores de data / hora no back-end. Quando você vê algo como 8/1/2014 15:26:57 em uma célula que foi configurada para formatação de data / hora, o Excel está realmente armazenando o valor como 41852.64372 - isso representa o número de dias (com a parte decimal sendo uma fração de um dia) desde aproximadamente 1/1/1900 0:00:00 (é um pouco mais complicado do que isso, mas isso já é mais do que você precisa saber).
HORA e MINUTO são funções (sim, há um SEGUNDO - e um DIA, MÊS e ANO se você precisar deles) que retornam suas respectivas porções a partir de um número de série de data / hora.
Então, o que estamos fazendo acima é pegar os horários de A1 & B1, zerando os segundos de cada um deles, e então fazendo a subtração.