Calcular el número de días de un mes

En una nota anterior, obtuvimos la siguiente fórmula para obtener la fecha del último día de un mes:

=FECHA(AÑO(A2), MES(A2)+1, 0)

Y para conocer el número de días de un mes, utilizamos esta otra:

=DIA(FECHA(AÑO(A2), MES(A2)+1, 0))

La cual está basada en funciones estándar o nativas de Excel. Sin embargo, si tenemos instalado el complemento Herramientas para análisis, podemos utilizar la función FIN.MES. La sintaxis es:

FIN.MES(fecha, num_de_meses)

El argumento fecha es la fecha inicial del cálculo. El argumento num_de_meses es un número entero que indica el número de meses posteriores a la fecha inicial, cuyo número de días queremos calcular. Por ejemplo, si tenemos en la celda A1 la fecha 05/02/2008, y queremos saber el número de días de ese mes (es decir, cero meses posteriores), utilizaremos la fórmula:

=FIN.MES(A1, 0)

La cual devuelve 29. Si queremos saber el número de días de marzo (un mes posterior) utilizaremos:

=FIN.MES(A1, 1), resultando 31.

Número de días hábiles entre dos fechas

Q: ¿Cómo puedo calcular el número de días hábiles entre dos fechas, es decir, excluyendo sábados y domingos?

A: Utilizando la función DIAS.LAB, incluída en el complemento Herramientas para Análisis de Excel.

DIAS.LAB no es una función nativa de Excel, sino que pertenece al complemento mencionado. Un complemento (add-in, en inglés), es un "miniprograma" o conjunto de características que no tiene de manera predeterminada Excel y que aumentan o extienden su funcionalidad. En el caso del complemento Herramientas para análisis, este incluye una serie de interfases y funciones para análisis financiero y científico.

Para poder utilizar las características del referido complemento, necesitamos instalarlo primero. Es sencillo: Ejecutamos Herramientas - Complementos... para abrir el cuadro de diálogo Complementos, y activamos la casilla de verificación correspondiente:

Aceptamos el cuadro.

Podemos ver ahora que el menú herramientas contiene un nuevo submenú llamado Análisis de datos, el cual contiene las nuevas funcionalidades instaladas:

Asimismo, podemos ver que tenemos nuevas funciones, incluyendo la que nos interesa, DIAS.LAB, que estará incluída en la categoría Fecha y hora en el cuadro Insertar función:

Seleccionamos la función y damos Aceptar.

Ingresamos nuestra fechas inicial y final como primeros dos argumentos. El tercer argumento, Festivos, lo usamos en caso de que queramos que Excel excluya los días festivos del cálculo. En caso afirmativo, elaboramos una lista en nuestra hoja con los días festivos que queremos que sean excluídos. Finalmente, ingresamos el rango de esta lista como argumento Festivos (o bién podemos ingresar las fechas directamente con la función FECHA). De cualquier forma, el argumento Festivos es opcional, podemos prescindir de él.

Si damos clic en el link Ayuda sobre esta función, veremos que Excel no dispone de ayuda para esta función, ya que el fabricante del complemento, no la incluyó en el mismo.

Calcular el último día de un mes

Ocasionalmente nos vemos en la necesidad de calcular la fecha del último día de determinado mes, de determinado año, o su pariente cercano, el número de días que tiene un mes.

En una previa nota vimos como calcular la fecha de término de un periodo particular, para un número entero de meses. La fórmula utilizada era:

=FECHA(AÑO(A2), MES(A2)+B2, DIA(A2)-1)

Donde A2 es la fecha inicial y B2 la duración del periodo en meses.

Si nuestro periodo está expresado en días, entonces usamos esta variante:

=FECHA(AÑO(A2), MES(A2), DIA(A2)+B2)

Esta fórmula funciona aún si DIA(A2)+B2 es mayor al número de días del mes MES(A2), ya que Excel "ajusta" el mes (y si es necesario, también el año) de la fecha resultante automáticamente. En efecto, si ingresamos la siguiente "fecha" en Excel:

33/01/2008

Excel nos mostrará:

02/02/2008

Ajustando el mes (y el día) adecuadamente para mostrar una fecha válida.

Supongamos ahora que en A2 tenemos una fecha de febrero de 2008, por ejemplo:

14/02/2008

Y queremos saber cuántos días tiene el mismo mes. Para calcularlo, primeramente calculamos la fecha del primer día del mes siguiente, como sigue:

=FECHA(AÑO(A2), MES(A2)+1, 1)

Lo cual devuelve 01/03/2008. Llegados a este punto, les revelaré algo que me llevó 10 años de meditación profunda descubrir: El último día de un mes es siempre el día anterior al primer día del mes siguiente. Conociendo esto, y sabiendo que en Excel, un día es igual a una unidad, obtenemos la fórmula para calcular el último día de un mes, en este caso febrero 2008:

=FECHA(AÑO(A2), MES(A2)+1, 1) - 1

Nuevamente, como un día es igual a una unidad, podemos restar 1 directamente al argumento día:

=FECHA(AÑO(A2), MES(A2)+1, 1-1)

Simplificando:

=FECHA(AÑO(A2), MES(A2)+1, 0)

Lo cual se convierte en:

=FECHA(2008, 3, 0)

Como el cero de marzo no es una fecha válida, Excel la ajustará adecuadamente y nos mostrará el día anterior al 1 de marzo:

29/02/2008

Para finalizar, si solo nos interesa saber el número de días de un mes (no una fecha) lo extraemos de la fórmula anterior con la función DIA:

=DIA(FECHA(AÑO(A2), MES(A2)+1, 0))

Determinar una fecha de vencimiento

Un usaurio me pregunta como calcular la fecha de término de los contratos que elabora. Es decir, si en la celda A2 tiene una fecha de inicio de contratación del 05/02/2007 y en la celda B2 el plazo del contrato en meses, por ejemplo 10, ¿como hacer para que en la celda C2 aparezca 04/12/2007?

Para obtener la respuesta es necesario conocer la función FECHA. También habremos de utilizar las funciones AÑO, MES y DÍA. Rápidamente, diremos que MES (la más conocida) devuelve el número de mes en una fecha; en nuestro ejemplo, MES(A2) devuelve 2. De manera similar, AÑO(A2) devuelve 2007 y DÍA(A2) devuelve 5.

La función FECHA nos devuelve una fecha, dados los argumentos año, mes y día. La sintaxis es la siguiente:

=FECHA(año, mes, día)

El siguiente ejemplo devuelve 01/01/2007:

=FECHA(2007, 01, 01)

Aparentemente se trata de una función muy poco práctica (¿por qué no escribir la fecha directamente?...)

No obstante, tenemos la gran ventaja de que, como en cualquier otra función de Excel, los argumentos ingresados pueden ser otras fórmulas, o bien, referencias a celdas. Al tratar con fechas, esto tiene un enorme valor. Retomando el ejemplo anterior, si en un primer intento consideramos meses de 30 días y sumamos B2*30, obtenemos:




Esto no nos da la fecha exacta de terminación (04/12/2007) por la sencilla razón de que no todos los meses tienen 30 días. Pero si partimos nuestra fecha inicial en los tres argumentos de la función FECHA, simple y sencillamente sumamos 10 (o su referencia B2) al argumento "mes", como sigue:

=FECHA(2007, MES(A2)+10, 5), o mejor:

=FECHA(AÑO(A2), MES(A2)+B2, DÍA(A2))

La cual se convierte en:

=FECHA(2007, 2+10, 5), y a su vez en =FECHA(2007, 12, 5)

Resultando 05/12/2007.

Ahora bién, nosotros buscábamos 4 de diciembre, no cinco. ¿Qué hacemos? Tan fácil como restar 1 al argumento "día":

=FECHA(AÑO(A2), MES(A2)+B2, DÍA(A2)-1)


Lo cual ya produce 04/12/2007. Y como esta es una fórmula general, que funciona con cualquier otra fecha inicial o periodo, vemos la gran utilidad que tiene la función FECHA.

Fechas y horas en Excel. Introducción II

Continúa de la nota anterior.

Con las horas, Excel maneja otra estrategia. Lo que hace es considerar una hora de un día como una fracción de ese día. Así, el mediodía del 17 de octubre de 2007, escrito así: 17/10/2007 12:00 p.m., corresponde al número de serie 39,372.5; las 6:00 a.m del mismo día corresponden al 39,372.25; y las 6 p.m. corresponden al 39,372.75. Si en la celda A2 tenemos 17/10/2007 06:00 p.m., y en B2, 17/10/2007 12:00 p.m por ejemplo, calculamos la diferencia de horas con la fórmula:

=A2 - B2

Como por lo regular Excel expresa el resultado con el mismo formato que los argumentos, el resultado obtenido es 00/01/1900 06:00. De las 12:00 pm a las 6:00 pm del día 17 han transcurrido cero días y seis horas. Como no tiene mucho sentido hablar de cero días o de cero de enero de 1900, aplicamos el formato hh:mm a nuestra celda (Ctrl + 1, Ficha Número), de forma que nos muestre 06:00.

Aunque estamos claros que internamente Excel ve el valor 0.25, ¿cierto? (39,372.75 - 39,372.5).
Cuando manejamos horas no asociadas a un día específico, Excel no les asigna ninguna parte entera. Por ejemplo, la hora 3:35, así, sin fecha, la convierte internamente a 0.15. Es por ello que necesitamos al cero de enero de 1900 mencionado anteriormente.

Supongamos que administramos un café internet utilizando una hoja de Excel. Nuestro modelo tiene tres columnas, la primera para las horas de entrada, la segunda para las horas de salida y la tercera para el tiempo total consumido. En la celda A2 tenemos 12:00 p.m., y en B2, 06:00 p.m. Usemos esta fórmula en en C2:

=B2 - A2

La cual devuelve 06:00. Seis horas. El tiempo total consumido por nuestro usuario imaginario.

Ahora supongamos que cobramos $10.00 por hora. Para saber cuanto debemos cobrar al usuario normalmente usaríamos la siguiente fórmula:

=C2*G1

Sin embargo, el resultado, el cual esperaríamos que fuera $60.00, es ¡$2.50! Esto, que a primera vista podría parecer algo incomprensible, es, no obstante, algo perfectamente lógico y normal. ¿Por qué? Porque internamente Excel realizó la operación 0.25*10, no 6*10.

Estarán de acuerdo en que no es posible tratar a las horas y minutos como números decimales. Si queremos multiplicar una hora por un número decimal, como es el caso, primero debemos convertir dicha hora a número decimal. Vamos por partes:

Primero demos formato de número con dos decimales a la celda C2, para que nos muestre:

O.25

Este es el número de días consumidos por el usuario. Recordemos que en Excel un día equivale a una unidad. Seis horas son 0.25 días. Entonces, si queremos conocer la respuesta en horas "decimales", simplemente multiplicamos este resultado por el número de horas que tiene un día, 24:

=C2*24 = 0.25*24 = 6

Ahora sí, multiplicamos este resultado por el costo por hora, y obtenemos $60.00.

=(C2*24)*G1

Fechas y horas en Excel. Introducción

Realizar cálculos con fechas y horas suele ser una actividad altamente frustrante para el usuario principiante, debido principalmente al desconocimiento que tiene de la forma en que Excel maneja las fechas y las horas. No tiene por qué ser así. Si bien es cierto que Excel tiene algunas limitaciones con las fechas, también es cierto que tiene muchísimas capacidades.

Para Excel, una fecha es simplemente un número. Más exactamente, un número de serie. Para demostrarlo, escribamos en una celda cualquiera la fecha de hoy, con la fórmula =HOY(), o presionando Ctrl + Shift + ; o bien manualmente escribimos 17/10/2007.

Ahora le damos formato de número con cero decimales (Formato - Celdas (o Ctrl + 1), ficha Número, y en la lista escogemos "Número"). Lo que veremos en nuestra celda es el número 39,372. Esto significa que para Excel, el 17 de octubre de 2007 es el número de serie 39,372 en su sistema; de la misma forma, el 16 de octubre es el 39,371, el 15 es el 39,370, el 14 es el 39,369... y así hasta llegar al uno de enero de 1900 que es el número 1 en la serie. El sistema de fechas de Excel comienza el 1 de enero de 1900 y termina el 31 de diciembre del 9999, correspondiente al serial 2,958,465 (en realidad comienza el cero de enero de 1900, correspondiente al 0 en la serie. Escriban la fecha 00/01/1900 y Excel no reclamará nada. Más adelante veremos para que necesitamos esta fecha inexistente). Entonces, cada que escribimos una fecha, Excel en realidad mira su correspondiente número de serie.

Solo así Excel podría hacer cálculos con fechas.

Ya que conocemos lo anterior, se vuelven evidentes los siguientes procedimientos:

Supongamos que en la celda A1 tenemos 17/10/2007, y en A2, 01/10/2006.

Para conocer cuántos días han pasado entre estas dos fechas, simplemente restamos la fecha más antigua de la más reciente:

= A1-A2 = 381

Para saber cuantas semanas han transcurrido entre estas dos fechas, restamos la fecha más antigua de la más reciente, y dividimos el resultado por 7:

=(A1 -A2 )/7=54 (cambiamos el formato si es necesario)

Para saber cuántos años han transcurrido, restamos la fecha más antigua de la más reciente, y dividimos el resultado por 365:

=(A1-A2 )/365 = 1

Continuamos en la siguiente nota.