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:
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
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.
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.
Adjunto fichero para mejor análisis:
| ...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
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.
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.
Adjunto fichero para mejor análisis:
 |
| Fechas desglose.xl... |
| Hosted by eSnips |
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??
ResponderEliminarHola!!
ResponderEliminarquizá 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