domingo, 7 de noviembre de 2010

Funciones de fechas en Excel.

A primeros de año subí un post explicando cómo operar para calcular diferencias de días entre fechas (ver), y fue precisamente en este post sobre el que recibí una cuestión adicional de un lector a través de un comentario:

...yo parto de un intervalo de fechas, q pueden iniciarse o finalizar en cualquier día del mes, por estas tres líneas de datos en columnas A1-B1
05/12/1965-14/04/1995
10/05/1995-26/02/1996
22/03/1996-26/10/2010
Lo q necesito es saber los días por mes de cada año en esas tres fechas. Hasta ahora he conseguido descomponer esas tres líneas por año, desde el 65 al 2010, pero saber los días por mes, nada...


Para resolver esta cuestión emplearé diferentes funciones estándar de Excel relativas a fechas, como son:
FECHA(año; mes; día)
y FIN.MES(fecha; meses)

Analizaremos, desglosando el intervalo de dos fechas dadas (Fecha inicial y Fecha final) por días transcurridos en cada mes y para cada año.

Partiremos de los dos siguientes fechas a estudiar:
Fecha inicio= 05/12/2009
Fecha fin= 05/11/2010

Funciones de fechas en Excel.


definiendo, por comodidad, ambas celdas con nombres:
Ffin =Hoja1!$B$1
Fini =Hoja1!$A$1

La labor consiste en definir, con nuestras funciones de fechas una fórmula,el recorrido por meses el intervalo entre nuestras dos fechas, así como ir calculando al tiempo los días correspondientes a cada mes.
Para conseguir esto he construido tres fórmulas diferentes; la primera de ellas me define la Fecha inicial, sobre la que se calcularán los distintos comienzos de cada mes hasta alcanzar el último del intervalo (en la celda A2):
=SI(FECHA(AÑO(A1);MES(A1);1)<=Fini;Fini;SI(FECHA(AÑO(A1);MES(A1);1)<=Ffin;FECHA(AÑO(A1);MES(A1);1);""))

sobre esta celda formulamos las demás en la columna A:
=SI(FECHA(AÑO(A2);MES(A2)+1;1)<=Fini;Fini;SI(FECHA(AÑO(A2);MES(A2)+1;1)<=Ffin;FECHA(AÑO(A2);MES(A2)+1;1);NOD()))
en la que nos devuelve el día 1 del mes siguiente a la última fecha. Simplemente arrastraremos la fórmula hasta alcanzar la Fecha final.
Para fechas posteriores, fuera del intervalo dado, la fórmula nos devolverá un error tipo #N/A, que podremos no hacer visible con un formato condicional.

Funciones de fechas en Excel.



Para concretar la fecha de fin de mes para cada una de las fechas anteriormente calculadas, simplemente emplearemos la función FIN.MES, pero con cuidado de condicionar la existencia de una fecha final a estudio. En la celda B2 y después arrastrando la fórmula hacia abajo:
=SI(FIN.MES(A2;0)>=Ffin;Ffin;FIN.MES(A2;0))

Para terminar, en la columna C, añadimos una diferencia para cada fila entre las dos fechas.

Funciones de fechas en Excel.


Adjunto fichero para mejor análisis:
Fechas desglose
Fechas desglose.xl...
Hosted by eSnips

2 comentarios:

  1. Hola que tal es exactamente lo que he buscado ya que no podia hacer la formula pero.......... lo estoy haciendo como indicas y me marca que esta mal :( realmente no se excel asi que pues no encuentro el error m puedes ayuda??

    ResponderEliminar
  2. Hola!!
    quizá deberías descargarte el fichero adjunto en el post y revisar bien los pasos dados; como podrás comprobar en este ejemplo, los pasos son sencillos.
    Para ayudarte necesitaría saber ¿que tipo de error es el que te da?.
    Intenta analizar el ejemplo adjunto al post, y si no das con el error, envíame tu fichero a
    excelforo@gmail.com
    y lo revisaré.
    Un saludo

    ResponderEliminar