Outra pergunta do meu seminário Atlanta Power Excel:
Como você pode arredondar o tempo faturável para o próximo quarto de hora?
Digamos que você mantenha um registro de seu tempo faturável. Você tem um horário de início e um horário de término. Uma fórmula como =E2-D2calculará o tempo decorrido.
O resultado em F2 é de 55 minutos. Mas a formatação padrão mostra 12h55. Escolha a coluna inteira. Pressione Ctrl + 1 para formatar células. Selecione a categoria de tempo. Qual destes você deve usar? Por enquanto, escolha 13:30:55, mas você verá mais tarde o problema com esta seleção.
A política da sua empresa é arredondar todas as faturas até o próximo quarto de hora. Faça este cálculo: o dia tem 24 horas. São 4 horas quarto em uma hora. Multiplique 24 * 4 para obter 96. Isso significa que um quarto de hora é equivalente a 1/96 de um dia.
Use a fórmula =CEILING(F2,1/96)para arredondar o tempo decorrido em F até o próximo quarto de hora. No método de armazenamento de tempo do Excel, Meio-dia é 0,5. 6AM é 0,25. 0h15 é 1/96 ou 0,010417. É mais fácil lembrar 1/96 do que 0,010417.
Os resultados são mostrados abaixo. Observe como os 35 minutos são arredondados para 45 minutos. Esta parece ser a fórmula que meu advogado usa … haha, isso é só uma piada Esquire Dewey! Não me processe! Eu sei que eles usam =CEILING(F2,4)…
Parando aqui … este artigo está arredondando para o próximo quarto de hora porque esse é o método que Larry está usando em meu seminário. Há uma boa chance de que sua empresa mude para outra coisa. A imagem abaixo mostra como arredondar para cinco minutos ou 1 minuto. Você pode usar uma lógica semelhante para arredondar para os 6 minutos mais próximos (1/240) ou 12 minutos (1/120).
Também - uma sugestão gentil que mostra a diferença entre FLOOR, MROUND e CEILING. A função FLOOR em B9: B22 sempre arredonda para baixo. O MROUND em C9: C22 gira para o mais próximo. O TETO em D9: D22 arredonda para cima.
Nota
A dica de ferramenta do Excel irá sugerir que você use FLOOR.MATH ou CEILING.MATH em vez de FLOOR ou CEILING. As novas versões ".Math" dessas funções lidam com números negativos de maneira diferente. Como o tempo no Excel nunca pode ser negativo, é bom usar o PISO ou TETO normais.
Altere a fração de 1/96 para 1/288 para arredondar para incrementos de cinco minutos.
Conversão de tempos em horas
Outra sugestão de Larry: converta os horários em horas reais. Dessa forma, ao multiplicar pela taxa de faturamento por hora, a matemática funcionará. Pegue o tempo decorrido arredondado e multiplique por 24 para obter um número decimal de horas. Certifique-se de formatar o resultado como um número com duas casas decimais e não uma hora.
Qual formato de hora usar?
É hora de falar sobre essas opções de formatação de hora: Você poderia ter escolhido 13:30, 13:30:55 ou 37:30:55. Nas imagens acima, usei 13:30:55. O problema com isso aparece no final da semana. Quando você totaliza as horas decimais na coluna H, tem 53 horas faturáveis. Quando você totaliza os tempos em G, obtém cinco horas. O que está acontecendo?
Ambos estão corretos, se você entender o formato de número que selecionou. Digamos que você entre =NOW()em uma célula. Você pode formatar a célula para mostrar a data e hora, apenas a data ou apenas a hora. O Excel não tem nenhum problema em deixar de fora a parte de 29 de maio às 8h15min34s se você solicitar que ele faça isso. A resposta na célula G57 é "2 dias e cinco horas". Mas quando você escolheu 13:37:55 como o formato de hora, estava dizendo ao Excel para deixar de fora o número de dias e mostrar apenas as horas, minutos e segundos.
Se você converter a coluna G para o formato 37:30:55, obterá o número correto de horas:
A legenda acima dessa imagem captura o pequeno problema com o formato 37:30:55: ela está mostrando segundos, o que parece desnecessário, pois estamos arredondando para os quinze minutos mais próximos. Após escolher 37:30:55, clique na categoria Custom. Você pode ver o código de formatação de número (h):mm:ss;@usado. Os colchetes ao redor do H são a parte importante.
Para mostrar seus tempos em horas e minutos, edite o formato do número para remover o: ss.
Assistir vídeo
O vídeo de hoje contém uma dica bônus no final para formatar decimais como uma fração.
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2210: Arredondando para o Próximo Quarto de Hora.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Hoje, outra pergunta do meu seminário Atlanta Power Excel. Alguém estava fazendo uma planilha de faturamento e queria arredondar para o próximo quarto de hora, certo? Então, para cada tarefa aqui, eles têm um Cliente, Hora de Início e Hora de Fim, e para calcular o Tempo Decorrido - isso é simples, se forem tempos reais ou se forem apenas = E2-D2. Então, isso significa 55 minutos, mas está dizendo de uma forma muito estranha - "12:55 AM." Queremos ir em frente e formatar isso. Escolha a hora e, ei, olhe, há algumas opções diferentes aqui: 13:30; 13:30:55, que mostraria os segundos; e então 37:30:55. Agora, por enquanto, vou escolher 13:30, mas vamos circular de volta mais tarde e reexaminar essa escolha. Então, eu escolho OK e clico duas vezes para copiá-lo,e você vê que ele está apenas fazendo a matemática - então aqui, 25 minutos. Mas, a pessoa que fez esta pergunta - a empresa para a qual trabalham - eles querem arredondar para o próximo quarto de hora.
Tudo bem, quinze minutos. Vamos pensar nos tempos no Excel. Meio-dia é 0,5. Se eu formatar isso como uma hora - 12:00:00 PM. E se eu fizer 0,25, será 6h00. Tudo bem, então a forma como isso funciona é que um dia inteiro é o número 1.0 e, se quiséssemos descobrir 15 minutos, seria-- há 24 horas x 4 horas quinze minutos, então teríamos que colocar = 1/96 aí, e isso nos dá 15 minutos. Agora, 1/96 - posso fazer aquela matemática na minha cabeça: 24x4; 1/96 - certamente é mais fácil lembrar 1/96 do que lembrar este decimal: 0,01041666667. Tudo bem, era mais fácil usar 1/96 lá, então vamos usar uma função chamada CEILING.
TETO. Veja, nós temos uma escolha entre CEILING e CEILING.MATH para números positivos que são idênticos. É mais curto digitar CEILING, então vou usar CEILING. E, arredondado, há 1/96 de um dia - que é 15 minutos - e essa é a resposta certa, no formato errado. Vamos copiar esses formatos - Ctrl + C e Alt + E + S + T para colar formatos especiais - e os 55 minutos são arredondados para a próxima hora. Clique duas vezes para copiar isso e, veja, os 35 minutos sobem para os próximos 45; 25 arredondamentos para os próximos 30. Parece estar funcionando, embora a pessoa que fez esta pergunta-- eles estejam arredondando para os próximos 15. Talvez até sua empresa use algo diferente.
Tudo bem, então, para arredondar para o quarto de hora mais próximo, usamos 1/96. Mas para arredondar para os 5 minutos mais próximos - há 24 horas, há 12 períodos de 5 minutos em cada hora, então 12x4 = 288. Use 1/288 para arredondar para os 5 minutos mais próximos. Se você arredondar para os 6 minutos mais próximos - há 10 períodos de 6 minutos x 24 horas; você arredondar para o 1/240 mais próximo. Para arredondar para o minuto mais próximo, escolha o 1/1440 mais próximo.
Tudo bem, então você pode fazer isso, e também temos uma escolha. TETO sempre vai arredondar para cima, então 16 minutos - vamos para 1/96 aí - 16 minutos vai arredondar para 30 e, embora eu tenha certeza de que meu advogado acha que é uma ótima maneira de cobrar, é não é realmente justo para mim - o cliente. Tudo bem, então você sabe que CEILING vai arredondar. Meu advogado nunca vai usar isso: Este PISO sempre arredondará para baixo. Mas, provavelmente o mais justo aqui é usar o MROUND. Portanto, = MEIO do tempo para o 1/96 mais próximo, e isso será arredondado para tudo até cerca de 22,5 minutos; e então, depois disso, ele vai arredondar. Tudo bem, então é arredondado para o que estiver mais próximo. Então, você tem que escolher se vai usar FLOOR, MROUND ou CEILING, e então qual será o seu divisor - seja ele 'vai ser nos próximos 5 minutos, no minuto mais próximo ou, você sabe, o que for. Tudo bem, então estamos arredondando.
Agora, uma dica bônus aqui - era o Larry naquele seminário - Larry diz: "Ei, olhe, não é muito bom mostrar o tempo faturável em momentos como esse; realmente queremos converter para horas porque você realmente vai para cobrar a alguma taxa - você sabe, $ 35 / hora, $ 100 / hora, $ 500 / hora, qualquer que seja sua taxa - e precisamos ser capazes de multiplicar as horas x as taxas. Então, seria bom pode converter essas horas em uma hora decimal. Então, pegamos esse tempo no G2 e o multiplicamos por 24, e isso nos dará um número decimal de horas, mas estará no formato errado. Eles vamos escolher um formato de hora neste caso, quando não deveria ser um formato de hora; deveria ser apenas no formato de número. E certifique-se de incluir pelo menos 2 casas decimais. Clique duas vezes para copiar, e obtemos nossos tempos. "
Tudo bem, agora, voltando à nossa pergunta aqui de "Qual formato de número devemos usar?", Este é um lugar realmente bom para falar sobre "Por quê?". Se escolhermos todos esses números, aqui, veremos que a soma é 53 horas, e se eu descer aqui e inserir uma função SUM, teremos 53 horas. Perfeito. Mas, se estou usando a função SUM abaixo de uma coluna de tempo e faço Alt + = ou AUTOSUM, isso me dá 5 horas … 5 horas. Qual é a certa - 53 ou 5?
Bem, acontece que, de alguma forma estranha, os dois estão certos. Mas, aqui está porque ambos estão certos. Vou inserir uma função chamada = AGORA. Você verá que estou gravando isso no dia 9 de maio, então, 20 dias atrás, e são 7h08. E com = AGORA, às vezes eu quero mostrar a data e a hora, mas outras vezes, eu só quero para mostrar a data. Assim, posso fazer = AGORA me dar a data atual, ou posso fazer = AGORA me dar a hora atual. E assim, as funções de número ficam felizes em truncar a folga ou remover a data. E este que escolhemos aqui, 13:30, está dizendo, "Ei, eu não quero ver o número de dias; apenas me mostre a hora." Sim, esse é o problema - porque na verdade são 2 dias e 5 horas - 2 dias são 48 horas - 2 dias e 5 horas. E nós dissemos - dissemos ao Excel, "Ei, não se preocupe em me mostrar os dias. "Bem, não sabíamos que estávamos dizendo isso ao Excel, mas era o que estávamos dizendo ao Excel quando escolhemos este ou este. Tudo bem, então você é forçado a desça aqui para 37:30:55. Esse é o que vai nos mostrar o número total de horas, e obtemos a resposta correta de 53 horas.
Mas, parece muito bobo mostrar esses segundos extras quando estamos arredondando para 15 minutos. Então, vamos voltar a isso. Depois de escolher 37:30:55 para aprender o formato do número por trás disso, clique em Personalizar. Você verá que o que o faz funcionar são os colchetes em torno da hora. Mas, não precisamos do: ss. Portanto, agora podemos editar esse formato personalizado e, portanto, temos horas e minutos. Não precisamos entender hoje o que; @ significa. Está dizendo que, para texto, basta mostrar o texto; mas, apenas deixe essa parte lá, mesmo que você não entenda. Clique em OK e agora temos nosso tempo em horas e minutos, omitindo os segundos. Mas, os totais funcionam. Realmente, muito sutil, 13:30 e 13:30:55. Você sabe, não diz: "Ei, você está dizendo ao Excel para ignorar a data … os dias inteiros." Mas,tu es.
Coisas como Taking Time Math estão neste livro: LIVe: The 54 Greatest Excel Tips Of All Time. Clique nesse "I" no canto superior direito para conferir.
Esta parecia uma pergunta fácil; Não acredito que já chegamos em 7 minutos. Porém, uma empresa deseja faturar em um quarto de hora e sempre deseja arredondar para o próximo quarto de hora. Portanto, 24 horas por dia, 24x4 = 96; 15 minutos = 1/96. Portanto, use CEILING (A2,1 / 96) para arredondar para o próximo quarto de hora superior. Use MROUND (A2,1 / 96) para arredondar para o quarto de hora mais próximo e então use FLOOR (A2,1 / 96) para arredondar para o quarto de hora inferior - sempre volta. O dia tem 24 horas, então você pode fazer 1/288 para chegar a períodos de 5 minutos, 1/240 para chegar a períodos de 6 minutos, 1/1440 para arredondar para o minuto mais próximo. E então, a dica bônus lá no final (qual formato de número) - você sempre quer voltar àquele 37:30:55 se for mostrar horas além de 24 horas.
Para baixar a pasta de trabalho do vídeo de hoje, visite aquele URL na descrição do Youtube.
Eu quero agradecer por você passar por aqui; nos vemos na próxima vez para outro netcast de.
Ei! Obrigado por aguardar. Mas, só mais uma dica bônus aqui (que nem vou me preocupar em mencionar). O que comecei aqui - vamos esclarecer tudo isso - uma pequena fórmula que apareceu e apontou para um desses decimais - então, o próximo quarto de hora. Mas, eu queria mostrar isso não como o 0,010417. É incrível - na Formatação de Número, há na verdade algo chamado Frações, e se eu escolher "Até dois dígitos", obterei 1/96; mas preciso lidar com 1/288, então escolho "Até três dígitos" e ainda me mostra 1/96; mas, então, quando eu converter para este - o 1/288 - ele mostrará corretamente. Agora, acho que deveria ter feito "quatro dígitos" para lidar com o 1/1440. O que isso vai fazer? Isso vai me dar … Mas,se eu for para o Formato de número, e vá para Personalizado, e apenas adicionar um ponto de interrogação extra lá, chegaremos ao 1/1440 mais próximo de uma fração. Formato numérico - coisa legal, legal.
Baixar arquivo Excel
Para baixar o arquivo excel: round-to-quarter-hour.xlsx
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Amigos não permitem que amigos usem INDIRETO"
Jordan Goldmeier
