Cálculo de Número de Días

"Necesito calcular, cada día, cuántos días llevan transcurridos desde el 1 de enero del año en curso y los días que faltan para concluir el año ¿hay alguna función que realice estos cálculos?"

No existe ninguna función directa que realice estos cálculos, pero la solución es bien sencilla utilizando las funciones FECHA y AÑO. Supongamos que deseamos contar con la siguiente información actualizada diariamente:

1. la fórmula de B2 es muy sencilla:

=HOY()

2. La fórmula de B3 es igualmente sencilla utilizando la función NUM.DE.SEMANA Esta función tiene tan sólo dos argumentos NUM.DE.SEMANA(Num_de_serie;Tipo). El primer argumento se refiere a la fecha de la que queremos saber el número de semana del año que le corresponde. El segundo argumento sirve para determinar el tipo de semana, esto es, si la semana comienza el domingo, en cuyo caso escribiremos el valor 1, o si queremos considerar semanas cuyo primer día sea el lunes, en cuyo caso el valor de este argumento será 2. Así las cosas, la fórmula resultante será:

=NUM.DE.SEMANA(B2;2)

3. Para calcular los días transcurridos desde el 1 de enero del año en curso hasta la fecha actual, utilizamos la siguiente fórmula:

=B2-FECHA(AÑO(B2);1;1) Si queremos añadir un texto descriptivo al resultado podemos utilizar el operador & (CONCATENAR):

=B2-FECHA(AÑO(B2);1;1)&" días desde principio del año"

4. Finalmente para saber cuántos días restan para concluir el año en curso:

=FECHA(AÑO(B2);12;31)-B2 Añadiendo un texto descriptivo sería:

=FECHA(AÑO(B2);12;31)-B2&" días para terminar el año"

Si queremos mantener esta información siempre visible en la hoja, podemos utilizar la opción de Inmovilizar Paneles. Para ello nos situamos en la celda A7 y vamos al menú Ventana/Inmovilizar Paneles. Aparecerá un borde superior en toda la fila 7 quedando inmovilizadas las celdas que se encuentren por encima de dicha linea.

Cálculo de la Edad en Años

"Partiendo de un listado con la fecha de nacimiento del personal necesito calcular su edad en años. Haciendo uso de la función AÑO no obtengo el resultado requerido ya que no tiene en cuenta fechas exactas".

Efectivamente, si necesitamos calcular la edad en años de un individuo, la función AÑO no nos proporcionará, por diferencia entre fecha de nacimiento y la fecha actual, el resultado deseado ya que no considera ni meses ni días. Por ejemplo, si tomamos como base el día de hoy, 2 de junio de 2010, y queremos saber cuántos años tiene alguien que nació el 28 de noviembre de 1969, la fórmula =AÑO(A1)-AÑO(A2) -suponiendo que en A1 tenemos la fecha actual y en A2 la fecha de nacimiento- nos devolverá un resultado de 41 años, que es la diferencia entre 2010 y1969. Es decir, que la operación que realiza la fórmula mostrada es 2010-1969=41. Pero evidentemente esta no es la edad del individuo en cuestión, ya que a 2 de junio todavía no habrá cumplido los 41.

Para realizar el cálculo correctamente utilizaremos la función FRAC.AÑO. Supongamos la siguiente lista:

Nos situamos en la celda D4 y escribimos la fórmula:

=FRAC.AÑO(C4;HOY();1)

La función FRAC.AÑO calcula la fracción de año que representa el número de días enteros entre una fecha inicial y una fecha final. Su sintaxis es FRAC.AÑO(Fecha_inicial;Fecha_final;Base). En nuestro ejemplo utilizamos como fecha inicial la fecha de nacimiento del empleado y como fecha final la generada por la función HOY(), es decir, el día actual. Una vez copiada la fórmula de D4 en el rango D5:D13 el resultado es el siguiente:

Para evitar los números decimales podemos utilizar la función TRUNCAR. En la celda E4 escribimos:

=TRUNCAR(D4) y copiamos hasta E13:

Evidentemente podemos resolver todo el problema con una sola fórmula. En F4 escribimos:

=TRUNCAR(FRAC.AÑO(C4;HOY();1))

Generar un Calendario Automático de Pagos

Descargar el Archivo

Hoy vamos a ver cómo podemos generar un calendario automático que nos indique, por ejemplo, cuándo debemos cobrar una venta a plazos. Partiremos de una fecha inicial, que será la fecha pactada con el cliente, además del número de años al que se va a realizar la operación y el número de pagos al año que el cliente debe atender. Con estos tres datos generaremos un calendario automático con las correspondientes fechas de pago.

Para desarrollar este modelo utilizaremos, entre otras, varias funciones de la categoría denominada Fecha y Hora. A saber: FECHA, FIN.MES, AÑO, MES, DIA. Nuestra entrada de datos será la siguiente:

Lo que queremos conseguir es que el calendario que se muestra en la siguiente imagen se rellene automáticamente con el número de pago (o cobro) a realizar y la fecha de vencimiento, en base a los datos introducidos en B2:B4. Más concretamente queremos que si la fecha de inicio coincide con el último día del mes los siguientes vencimientos también lo hagan. Por otro lado, si la fecha es cualquier otro día del mes queremos que mantenga dicho día para el resto de la operación.

Para resolver este "problema", sin hacer la demasiado habitual chapucilla (y por favor que no se me moleste nadie) de sumar 30 días al mes anterior o soluciones similares, es necesario manejar algunas funciones de la categoría Fecha y Hora que paso a describir:

FIN.MES(Fecha_inicial;Meses)   Devuelve la fecha (como número de serie) del último día del mes anterior o posterior, respecto al argumento Fecha_Inicial, en función de que el argumento Meses sea positivo o negativo.

AÑO(num_de_serie)   Devuelve el año correspondiente a una fecha.

MES(num_de_serie)   Devuelve el mes de una fecha.

DIA(num_de_serie)     Devuelve el día de una fecha.

FECHA(Año;Mes;Dia)  Tal y como indica la ayuda de Excel, devuelve el número de serie secuencial que representa una fecha determinada. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Dentro de los argumentos de esta función es importante destacar dos cuestiones que nos indica la ayuda de Excel: Mes es un número que representa el mes del año. Si el mes es superior a 12, el mes agrega ese número de meses al primer mes del año especificado. Por ejemplo, FECHA(2008;14;2) devuelve el número de serie que representa la fecha 2 de febrero de 2009. Día es un número que representa el día del mes. Si el día es superior al número de días del mes especificado, día agrega ese número de días al primer día del mes. Por ejemplo, FECHA(2008;1;35) devuelve el número de serie que representa la fecha 4 de febrero de 2008.

Empezamos por generar el número de Pago:

1. Nos situamos en la celda A9 (en la celda A8 hemos dejado como dato fijo un uno porque como mínimo existirá un pago) y escribimos la siguiente fórmula:

=SI(A8<$B$3*$B$4;A8+1;"")

Fíjese que lo que estamos comprobando en A9 es si A8 (la celda inmediata superior) es menor que $B$*$B$4, es decir, que el número total de pagos a realizar. Si es menor le pedimos que sume uno. Si no es menor entonces es que la celda inmediata superior (en este caso A8) ya es igual que el número total de cuotas, en cuyo caso le ordenamos que no escriba nada en la celda (el doble par de comillas).

2. Copie la fórmula de A9 hasta la celda, por ejemplo, A43. La celda hasta la que debe copiar dicha fórmula dependerá de las opciones que usted maneje. En nuestro ejemplo he supuesto que el plazo máximo en años es de tres y el número máximo de pagos al año es de doce (3*12=36, que son las celdas en las que hemos copiado dicha fórmula). Con esta sencilla solución habremos conseguido que el número de pagos aparezca automáticamente en función de los datos que contemplemos en nuestra entrada de datos.

3. Nos situamos en la celda B8 y escribimos la fórmula =B2  para recuperar la fecha inicial de nuestra entrada de datos.

4. En la celda B9 escriba la siguiente fórmula:

=SI(A9="";"";SI(B8=FIN.MES(B8;0);FIN.MES(B8;12/$B$4);

FECHA(AÑO(B8);MES(B8)+12/$B$4;DIA(B8))))

Con el primer condicional comprobamos si en la celda de la izquierda, respecto a la que contiene esta fórmula) hay algo o no. En caso de que no haya nada (doble par de comillas) que no escriba nada. En caso contrario, es decir, que sí haya algo (un número de pago), comprobamos si la fecha inicial (que ya hemos trasladado a B8) es una fecha de fin de mes (31 de enero, 28 ó 29 de febrero, 31 de marzo, etcétera) o no. Si lo es, entonces le pedimos que genere la siguiente fecha sumándole a la fecha inicial el cociente entre 12/$B$4 ¿Qué significa esto? Significa que estamos calculando el número de meses de un año dividido por el número de pagos al año. Por ejemplo,  para pagos trimestrales (4 al año) la celda B4 contendrá un 4, con lo que el cálculo resultante será: 12/4=3  que es el número de meses que le sumará cada vez a la fecha inicial.

Finalmente (si la celda de la izquierda no está en blanco ni es una de las fechas de fin de mes del año) entonces le pedimos que  para componer la FECHA correcta utilice el AÑO de la celda anterior (inmediata superior); el MES de la celda anterior más 12/$B$4; y el DIA de la celda anterior. 

5. Copiamos esta fórmula y la pegamos hasta B43.

De esta manera, y como puede apreciar en las siguientes figuras, tanto el número de pago como el calendario de pagos aparecerán automáticamente en función de los datos que introduzcamos en nuestra entrada de datos:

Sugerencias:

* Como práctica puede intentar introducir Validación de datos en las entradas (rango B2:B4).

* También puede utilizar el artículo anterior si desea sombrear las filas pares.