Formato personalizado de números en MS Excel.

Cuando hablamos de formato de números nos referimos a como se ve un número en una celda de Excel. Al cambiar el formato de un número en una hoja de Excel, sólo estamos cambiando lo forma en que Excel exhibe el número. El número en si mismo no cambia.
Es fácil dar un ejemplo con la forma en que Excel exhibe las fechas, como mostré en mi entrada sobre fechas tiempo en MS Excel [link]. Allí vimos que cuando vemos la fecha "9/05/2006" en una celda, Excel "ve" el número 38846 que es la cantidad de días transcurridos desde el 1/01/1900 hasta el 9/05/2006.
Excel viene provisto con una gran cantidad de formatos para números, organizada en distintas categorías:

En esta entrada nos ocuparemos de la categoría "Personalizada".
Excel permite crear formatos de números para adaptarlos a nuestras necesidades. Para hacer esto activamos el menú de formato de celdas y elegimos la categoría "Personalizada". En la ventanilla "Tipo" escribimos el código que generará el formato deseado.

La estructura general del código se compone de 4 secciones:

Formato de números positivos; formato de números negativos; formato de ceros; formato de texto.

Por ejemplo, si queremos general un formato donde aparezca la palabra "Euros" inmediatamente después del número y los números negativos aparezcan entre paréntesis, usamos los códigos:

#,##0.00 "Euros";( #,##0.00) "Euros"

Si queremos que en este formato, en caso de que el valor sea 0 (cero) aparezca en la celda la frase "sin valor", agregamos la parte de formato de ceros

#,##0.00 "Euros";( #,##0.00) "Euros";"sin valor";

Si queremos que los números negativos aparezcan en rojo, podemos agregar el código [color] en la parte de números negativos:

#,##0.00 "Euros";[Rojo]( #,##0.00) "Euros";"sin valor";

En esta tabla se pueden ver los efectos de aplicar estos formatos:

Categorías: Varios_

Technorati Tags: Formato de numeros

Cálculos de tiempo con MS Excel – Funciones para cálculos de horas

En las últimas entradas he estado hablando sobre cálculos de tiempo con MS Excel, una explicación general sobre el método de calcular fechas y horas de Excel y en la entrada más reciente sobre el problema de "tiempos negativos". En el pasado también he escrito sobre sumas de tiempo con Excel.
En esta entrada haré una breve revisión de las distintas funciones de tiempo de Excel:

AHORA() - Devuelve el número de serie de la fecha y hora actuales. Esta función no utiliza ningún argumento. La función AHORA sólo cambia cuando se realiza un cálculo en la hoja de cálculo o cuando se ejecuta una macro que contiene la función. No se actualiza constantemente. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Esto quiere decir que el resultado de AHORA() aplicado hoy en una celda con formato General es "15/05/2006 18:17".
Si queremos obtener la hora del momento, sin la fecha, podemos usar la fórmula "=AHORA()-HOY()". Para ver el resultado de esta fórmula con formato de hora debemos dar este formato a la celda: "hh:mm".

HORA(núm_de_serie) – Da como resultado la hora correspondiente al número de serie (que indica horas, minutos segundos). Por ejemplo, =HORA(0.885) da 21:00, cuando 0.885 corresponde a la hora 21:14:24

HORANUMERO(texto_de_hora) - Devuelve el número decimal de la hora representada por una cadena de texto. Por ejemplo, =HORANUMERO("21:14:24") da como resultado 0.885.

MINUTO(núm_de_serie) - Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59. Siguiendo con nuestro ejemplo, =MINUTO(0.885) da como resultado 14.

SEGUNDO(núm_de_serie) - Funciona como MINUTO respecto a los segundos.

NSHORA(hora;minuto;segundo) - Devuelve el número decimal de una hora determinada. El número decimal que NSHORA devuelve es un valor comprendido entre 0 (cero) y 0, 99999999 que representa las horas entre 0:00:00 (00:00:00 a. m.) y 23:59:59 (11:59:59 p.m.). Por ejemplo, =NSHORA(21,14,24) da como resultado 0.885 (si la celda tiene formato General).
Lo interesante con esta función es que acepta valores aparentemente "inválidos" y los corrige. Por ejemplo, si en la fórmula anterior usamos "65" en lugar de "14" para los minutos, y usamos el formato de "h:mm AM/PM", veremos como resultado 10:05 PM, en lugar de 9:14 PM. Excel corrige automáticamente el exceso de minutos y los transforma en horas.

Categorías: Funciones&Formulas_

Technorati Tags: Fechas y Horas

Cálculos de tiempo con MS Excel – Diferencia de horas

En la entrada de ayer hablé sobre como Excel realiza cálculos de fechas y tiempo
Vimos que Excel representa las fechas como una serie de números enteros. Si trabajamos con la base del año 1900, el primer número de la serie, el 1, representa el 1 de Enero de 1900. Si usamos la base 1904, el 1 representa el 1 de Enero de 1904.
Las horas, minutos y segundos del día son representados por fracciones del número 1. De esta manera la hora 12:00 (mediodía) es representada por el número 0.5. Las seis de la tarde, 18:00, por el número 0.75.
Cuando efectuamos cálculos de fechas y tiempos, de hecho estamos trabajando con números, que Excel representa con formatos especiales como "dd-mm-yy", "h:mm:ss", o "dd-mmm-yyyy hh:mm". Cuando vemos la fecha 9-05-2006 en una celda, Excel "ve" el número 38846. Cuando queremos calcular cuantos días transcurrieron entre el 17-07-1991 y el 9-07-1992, restamos la última fecha de la primera. De hecho estamos efectuando la resta 33794-33436 = 358.
Si trabajamos con la base 1900 hay que tener en cuenta que Excel no "sabe" representar tiempos y fechas negativos.
Para aclarar este punto, mostraré un ejemplo de cálculo de lapsos entre dos horas.
Supongamos que queremos calcular el tiempo trabajado por operarios. Cuando el comienzo y el final de la jornada no caen en el mismo día, la hora del final es menor que la del principio y Excel no sabrá representar el resultado




El resultado ######## nos indica que Excel no sabe "traducir" la diferencia a formato de tiempo (tiempo negativo).
En este caso no podemos utilizar la función ABS (valor absoluto) a que el resultado sería incorrecto (ABS daría 16 horas en lugar de 8 horas).
En caso de tener que realizar este tipo de operaciones hay varias soluciones:
1 – Utilizar las horas con sus correspondientes fechas, como "08/05/2006 19:00" (ver en el archivo del ejemplo);
2 - Utilizar la fórmula que aparece en la fila 20 del ejemplo, =C20 + (C20"<"B20) - B20. En esta fórmula la expresión (C20"<"B20) puede dar como resultado 1 (verdadero) o 0 (falso). Si la hora del final es menor que la del comienzo, (C20"<"B20)=1, por lo tanto agregamos 1 a la hora del final, que es como agregar un día entero.
3 – El mismo efecto se puede conseguir con la fórmula que aparece en la fila 26 del ejemplo: =RESIDUO(C26-B26,1);


Categorías: Varios_


Technorati Tags: Fechas y tiempo en Excel