Otra pregunta de mi seminario de Atlanta Power Excel:
¿Cómo se puede redondear el tiempo facturable al siguiente cuarto de hora?
Diga que lleva un registro de su tiempo facturable. Tienes una hora de inicio y una hora de finalización. Una fórmula como =E2-D2calculará el tiempo transcurrido.
El resultado en F2 es 55 minutos. Pero el formato predeterminado muestra 12:55 a. M. Elija toda la columna. Presione Ctrl + 1 para dar formato a celdas. Seleccione la categoría Hora. ¿Cuál de estos debería usar? Por ahora, elija 13:30:55, pero verá más adelante el problema con esta selección.
La política de su empresa es redondear todas las facturas al siguiente cuarto de hora. Haga este cálculo: hay 24 horas en un día. Hay 4 cuartos de hora en una hora. Multiplica 24 * 4 para obtener 96. Eso significa que un cuarto de hora equivale a 1/96 de un día.
Utilice la fórmula =CEILING(F2,1/96)para redondear el tiempo transcurrido en F hasta el siguiente cuarto de hora. En el método de Excel para almacenar el tiempo, el mediodía es 0.5. 6AM es 0,25. 12:15 AM es 1/96 o 0.010417. Es más fácil recordar 1/96 que 0,010417.
Los resultados se muestran a continuación. Observe cómo los 35 minutos se redondean a 45 minutos. Esta parece ser la fórmula que usa mi abogado … jaja, ¡es solo una broma, Esquire Dewey! ¡No me demandes! Sé que usan =CEILING(F2,4)…
Haciendo una pausa aquí … este artículo se está redondeando al próximo cuarto de hora porque ese es el método que utiliza Larry en mi seminario. Existe una buena posibilidad de que su empresa se redondea a otra cosa. La siguiente imagen muestra cómo redondear a cinco minutos o 1 minuto. Puede usar una lógica similar para redondear a los 6 minutos más cercanos (1/240) o 12 minutos (1/120).
Además, una sugerencia suave que muestra la diferencia entre PISO, MROUND y TECHO. La función FLOOR en B9: B22 siempre redondea hacia abajo. El MROUND en C9: C22 se redondea al más cercano. El TECHO en D9: D22 redondea.
Nota
La información sobre herramientas de Excel le sugerirá que utilice FLOOR.MATH o CEILING.MATH en lugar de FLOOR o CEILING. Las nuevas versiones ".Math" de estas funciones manejan los números negativos de manera diferente. Debido a que el tiempo en Excel nunca puede ser negativo, está bien usar el PISO o el TECHO normal.
Cambie la fracción de 1/96 a 1/288 para redondear a incrementos de cinco minutos.
Conversión de tiempos a horas
Otra sugerencia de Larry: convierta los tiempos en horas reales. De esa manera, cuando lo multiplique por la tarifa de facturación por hora, las matemáticas funcionarán. Tome el tiempo transcurrido redondeado y multiplíquelo por 24 para obtener un número decimal de horas. Asegúrese de formatear el resultado como un número con dos lugares decimales y no una hora.
¿Qué formato de hora usar?
Es hora de hablar sobre esas opciones de formato de hora: podría haber elegido 13:30, 13:30:55 o 37:30:55. En las capturas de pantalla de arriba, usé 13:30:55. El problema con esto aparece al final de la semana. Cuando totaliza las horas decimales en la columna H, tiene 53 horas facturables. Cuando sumas los tiempos en G, obtienes cinco horas. Que esta pasando?
Ambos son correctos, si comprende el formato de número que seleccionó. Di que entras =NOW()en una celda. Puede formatear la celda para mostrar la fecha y la hora, solo la fecha o solo la hora. Excel no tiene ningún problema en omitir la parte del 29 de mayo del 29 de mayo a las 08:15:34 a.m. si le indica que lo haga. La respuesta en la celda G57 es "2 días y cinco horas". Pero cuando eligió 13:37:55 como formato de hora, le estaba diciendo a Excel que dejara el número de días y solo le mostrara las horas, minutos y segundos.
Si convierte la columna G para usar el formato 37:30:55, obtendrá la cantidad correcta de horas:
La leyenda sobre esa imagen captura el pequeño problema con el formato 37:30:55: muestra segundos, lo que parece innecesario ya que estamos redondeando a los quince minutos más cercanos. Después de elegir 37:30:55, haga clic en la categoría Personalizado. Puede ver que (h):mm:ss;@se utiliza el código de formato de número . Los corchetes alrededor de la H son la parte importante.
Para mostrar sus tiempos en horas y minutos, edite el formato de número para eliminar: ss.
Ver video
El video de hoy contiene una sugerencia adicional al final para formatear decimales como fracción.
Transcripción del video
Aprenda Excel del podcast, episodio 2210: redondee al siguiente cuarto de hora.
Oye, bienvenido de nuevo a Netcast, soy Bill Jelen. Hoy, otra pregunta de mi seminario de Atlanta Power Excel. Alguien estaba haciendo una hoja de cálculo de facturación y quería redondear al siguiente cuarto de hora, ¿verdad? Entonces, para cada tarea aquí, tienen un Cliente, una Hora de inicio y una Hora de finalización, y para calcular el Tiempo transcurrido, eso es simple, si esos son tiempos reales o si son simplemente = E2-D2. Entonces, eso es decir 55 minutos, pero lo está diciendo de una manera realmente extraña: "12:55 a. M.". Queremos seguir adelante y formatear esto. Elija Hora y, oye, mire, aquí hay algunas opciones diferentes: 13:30; 13:30:55, que mostraría los segundos; y luego 37:30:55. Ahora, por ahora, elegiré las 13:30, pero volveremos más tarde y volveremos a examinar esa opción. Entonces, elijo Aceptar y hago doble clic para copiarlo,y ves que solo está haciendo los cálculos, así que aquí, 25 minutos. Pero, la persona que hizo esta pregunta, la empresa para la que trabajan, quiere redondear al próximo cuarto de hora.
Muy bien, cuarto de hora. Pensemos en los tiempos en Excel. El mediodía es 0.5. Si lo formateo como una hora … 12:00:00 p.m. Y si hago 0.25, serán las 6:00:00 AM. Muy bien, la forma en que esto funciona es que un día completo es el número 1.0, por lo que, si quisiéramos calcular 15 minutos, sería: hay 24 horas x 4 cuartos de hora, así que tendríamos que poner = 1/96 allí, y eso nos da 15 minutos. Ahora, 1/96 - Puedo hacer esas matemáticas en mi cabeza: 24x4; 1/96: ciertamente es más fácil recordar 1/96 que recordar este decimal: 0.01041666667. Muy bien, fue más fácil usar 1/96 allí, así que vamos a usar una función llamada TECHO.
TECHO. Mira, tenemos una opción entre CEILING y CEILING.MATH para números positivos que son idénticos. Es más corto escribir CEILING, así que voy a usar CEILING. Y, redondeado, hay 1/96 de un día, que son 15 minutos, y esa es la respuesta correcta, en el formato incorrecto. Vamos a copiar estos formatos - Ctrl + C, y luego Alt + E + S + T para pegar formatos especiales - y los 55 minutos se redondean a la siguiente hora. Haga doble clic para copiar eso, y vea, los 35 minutos se redondean a los siguientes 45; 25 redondea a los próximos 30. Parece que está funcionando, aunque la persona que hizo esta pregunta, está redondeando a los próximos 15. Tal vez incluso su empresa utilice algo diferente.
Muy bien, para redondear al cuarto de hora más cercano, usamos 1/96. Pero para redondear a los 5 minutos más cercanos, hay 24 horas, hay 12 períodos de 5 minutos en cada hora, por lo que 12x4 = 288. Utilice 1/288 para redondear a los 5 minutos más cercanos. Si redondea a los 6 minutos más cercanos, hay 10 períodos de 6 minutos x 24 horas; redondeas al 1/240 más cercano. Para redondear al minuto más cercano, elija al 1/1440 más cercano.
Muy bien, entonces puedes hacer esto, y también tenemos una opción. TECHO siempre se redondeará, así que 16 minutos, vayamos a 1/96 allí, 16 minutos se redondearán a 30 y, aunque estoy seguro de que mi abogado cree que es una excelente manera de facturar, no es realmente justo para mí, el cliente. Muy bien, entonces sabes que el TECHO se redondeará. Mi abogado nunca va a usar esto: este PISO siempre se redondeará hacia abajo. Pero, probablemente lo más justo aquí es usar MROUND. Entonces, = MROUND del tiempo al 1/96 más cercano, y eso redondeará hacia abajo para todo hasta aproximadamente 22.5 minutos; y luego, después de eso, se redondeará. Muy bien, entonces se redondea a lo que sea más cercano. Por lo tanto, tiene que elegir si va a usar FLOOR, MROUND o CEILING, y luego cuál será su divisor, si es 'va a ser a los 5 minutos más cercanos, al minuto más cercano, o, ya sabes, lo que sea. Muy bien, ahí estamos reuniendo.
Ahora, un consejo adicional aquí, este era Larry en ese seminario, Larry dice: "Oye, mira, no es realmente bueno mostrar el tiempo facturable en momentos como ese; realmente queremos convertir a horas porque realmente vas facturar a alguna tarifa, ya sabes, $ 35 por hora, $ 100 por hora, $ 500 por hora, cualquiera que sea tu tarifa, y necesitamos poder multiplicar las horas por las tarifas. Por lo tanto, sería bueno poder convertir esas horas en un tiempo decimal. Por lo tanto, tomamos ese tiempo en G2 y lo multiplicamos por 24, y eso nos dará un número decimal de horas, pero estará en el formato incorrecto. En este caso, elegiremos un formato de hora, cuando no debería ser un formato de hora en absoluto; debería estar en formato numérico. Y asegúrese de incluir al menos 2 lugares decimales. obtenemos nuestros tiempos ".
Muy bien, ahora, volviendo a nuestra pregunta aquí de "¿Qué formato numérico deberíamos usar?", Este es un buen lugar para hablar sobre "¿Por qué?". Si elegimos todos estos números, aquí, veremos que la suma es 53 horas, y si bajo aquí y pongo una función SUM, obtenemos 53 horas. Eso es perfecto. Pero, si estoy usando la función SUM debajo de una columna de tiempo, y hago Alt + = o AUTOSUM, me da 5 horas… 5 horas. ¿Cuál es la correcta, 53 o 5?
Bueno, resulta que en un sentido extraño, ambos tienen razón. Pero, he aquí por qué ambos tienen razón. Voy a ingresar una función llamada = AHORA. Verá que en realidad estoy grabando esto el 9 de mayo, entonces, hace 20 días, y son las 7:08 am Y con = AHORA, a veces quiero mostrar la fecha y la hora, pero otras veces, solo quiero para mostrar la fecha. Entonces, puedo hacer = NOW dame la fecha actual, o puedo hacer = NOW dame la hora actual. Y así, las funciones numéricas están felices de truncar el tiempo libre o eliminar la fecha libre. Y este que elegimos aquí abajo, las 13:30, dice: "Oye, no quiero ver el número de días; muéstrame la hora". Sí, ese es el problema, porque en realidad son 2 días y 5 horas, 2 días son 48 horas, 2 días y 5 horas. Y lo dijimos, le dijimos a Excel: "Oye, no te molestes en mostrarme los días. "Bueno, no sabíamos que le estábamos diciendo eso a Excel, pero eso es lo que le estábamos diciendo a Excel cuando elegimos este o este. Está bien, entonces estás obligado a ven aquí a 37:30:55. Ese es el que nos mostrará el número completo de horas, y obtenemos la respuesta correcta de 53 horas.
Pero parece realmente tonto mostrar esos segundos extra cuando estamos redondeando a 15 minutos. Entonces, volvamos a eso. Después de elegir 37:30:55 para aprender el formato numérico detrás de esto, haga clic en Personalizado. Verá que lo que lo hace funcionar son los corchetes alrededor de la hora. Pero no necesitamos el: ss. Entonces, ahora podemos editar ese formato personalizado, y así tenemos horas y minutos. No necesitamos entender hoy qué significa; @. Está diciendo que, para el texto, simplemente muestre el texto; pero deja esa parte ahí, incluso si no la entiendes. Haga clic en Aceptar, y ahora tenemos nuestro tiempo en horas y minutos, dejando de lado los segundos. Pero los totales funcionan. Realmente, realmente sutil, esas 13:30 y esas 13:30:55. Ya sabes, no dice: "Oye, le estás diciendo a Excel que ignore la fecha … los días completos". Pero,usted está.
En este libro se encuentran temas como Tomar tiempo matemático: LIVe: Los 54 mejores consejos de Excel de todos los tiempos. Haga clic en esa "I" en la esquina superior derecha para comprobarlo.
Esto parecía una pregunta tan fácil; No puedo creer que ya llevamos 7 minutos. Pero una empresa quiere facturar en cuartos de hora y siempre quiere redondear al siguiente cuarto de hora. Entonces, 24 horas al día, 24x4 = 96; 15 minutos = 1/96. Entonces, use CEILING (A2,1 / 96) para redondear al siguiente cuarto de hora más alto. Use MROUND (A2,1 / 96) para redondear al cuarto de hora más cercano, y luego use FLOOR (A2,1 / 96) para redondear al cuarto de hora inferior; siempre vuelve atrás. Hay 24 horas en un día, por lo que puede hacer 1/288 para llegar a períodos de 5 minutos, 1/240 para llegar a períodos de 6 minutos, 1/1440 para redondear al minuto más cercano. Y luego, la sugerencia de bonificación al final (qué formato de número): siempre desea volver a 37:30:55 si va a mostrar horas en exceso de 24 horas.
Para descargar el libro de trabajo del video de hoy, visite esa URL en la descripción de Youtube.
Quiero agradecerles por pasar; nos vemos la próxima vez para otro netcast de.
¡Oye! Gracias por acompañarnos. Pero, solo un consejo adicional más aquí (que ni siquiera me voy a molestar en mencionar). Con lo que empecé aquí, aclaremos todo esto, una pequeña fórmula que vino y señaló uno de estos decimales, entonces, el cuarto de hora más cercano. Pero quería mostrar eso no como el 0.010417. Es increíble: en el formato de números, en realidad hay algo llamado Fracciones, y si elijo "Hasta dos dígitos", obtendré 1/96; pero necesito manejar ese 1/288, así que elijo "Hasta tres dígitos" y todavía me muestra 1/96; pero, luego, cuando me convierta a este, el 1/288, lo mostrará correctamente. Ahora, supongo que debería haber hecho "cuatro dígitos" para manejar el 1/1440. ¿Qué va a hacer eso? Eso me va a dar … Pero,si entro en el Formato numérico y en Personalizado y agrego un signo de interrogación adicional allí, llegaremos al 1/1440 de fracción más cercano. Formato numérico: algo genial, genial.
Descargar archivo de Excel
Para descargar el archivo de Excel: round-to-quarter-hour.xlsx
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Los amigos no permiten que los amigos usen INDIRECTO"
Jordan Goldmeier
