Cierto día comentando con un amigo me expuso un problema con el que se encontraba con cierta frecuencia en su trabajo, y es que debía modificar cada mes la serie de días del mes, de acuerdo con el mes del año en que se encontraba, para actualizar ciertos registros de datos asociados a cada día; se quejaba amargamente que lo tenía que cambiar a mano. Improvisé una solución con el tiempo se quedó como definitiva por su sencillez y comodidad.
Combinaremos lo aprendido con la herramienta Validación con la función FIN.MES(fecha_inicial; meses) y , por supuesto, con una función condicional SI.
En primer lugar en alguna celda de nuestra hoja de cálculo configuraremos dos celdas validadas, una que despliegue con la opción Lista los meses del año (de 1 a 12) y otra que nos despliegue algunos años (2008, 2009 y 2010); en ambos casos podemos optar por definirlo dentro de la herramienta de validación en lugar de referenciarlo.
El siguiente paso lógico es definir, en base a estas celdas con Validación de lista, la fecha del mes; para lo que emplearemos la siguiente función:
=FECHA($E$2;$E$1;1)
es decir,
=FECHA(Año;Mes;Día)
con lo que hemos creado, de forma móvil, nuestra fecha, concretamente el día 1 del mes y año deseado.
Será a partir de esta fecha desde donde generaremos el resto de días del mes del mes y año definidos con nuestra validación, sabiendo que en todo caso el máximo número de días de un mes es 31. Arrastraremos y copiaremos la siguiente función anidada en todas las celdas necesarias:
=SI(O(A6+1>FIN.MES($A$5;0);MES(A6+1)<>MES($A$5));"";A6+1)
lo más importante en este caso radica en discrimar todas aquellas fechas que no correspondan al mes de estudio, para lo que hemos dado como prueba lógica una función O
O(A6+1>FIN.MES($A$5;0);MES(A6+1)<>MES($A$5))
si la fecha anterior incrementada en un día es mayor al último día del mes analizado o el mes de la fecha difiere del mes de estudio; entonces aplicaremos un condicional SI
=SI(prueba lógica con O;"";fecha anterior + 1)
Como aclaración diré que la función FIN.MES(fecha; meses) nos devuelve la última fecha del mes solicitado, anterior o posterior a la fecha del número de mes indicado; en nuestro caso, al necesitar la fecha de fin de mes del mismo en que nos encontramos le asignamos un valor 0.
Obtenemos como resultado que los valores de fecha que no correspondan al mes de estudio aparecerán sin valor, que es lo que se pretendía.
Para aportar algo más de valor añadido al informe, en una columna adyacente a las fechas del mes, se incluye el día de la semana de cada fecha, empleando una función ya vista DIASEM
=SI(A7="";"";ELEGIR(DIASEM(A7;2);"Lun";"Mar";"Mie";"Jue";"Vie";"Sab";"Dom"))
para todas las celdas de la columna. Además podemos mediante un formato condicional colorear aquellas fechas que caigan en Sábado o Domingo:
El resultado lo podemos ver en el archivo adjunto.
Combinaremos lo aprendido con la herramienta Validación con la función FIN.MES(fecha_inicial; meses) y , por supuesto, con una función condicional SI.
En primer lugar en alguna celda de nuestra hoja de cálculo configuraremos dos celdas validadas, una que despliegue con la opción Lista los meses del año (de 1 a 12) y otra que nos despliegue algunos años (2008, 2009 y 2010); en ambos casos podemos optar por definirlo dentro de la herramienta de validación en lugar de referenciarlo.
El siguiente paso lógico es definir, en base a estas celdas con Validación de lista, la fecha del mes; para lo que emplearemos la siguiente función:
=FECHA($E$2;$E$1;1)
es decir,
=FECHA(Año;Mes;Día)
con lo que hemos creado, de forma móvil, nuestra fecha, concretamente el día 1 del mes y año deseado.
Será a partir de esta fecha desde donde generaremos el resto de días del mes del mes y año definidos con nuestra validación, sabiendo que en todo caso el máximo número de días de un mes es 31. Arrastraremos y copiaremos la siguiente función anidada en todas las celdas necesarias:
=SI(O(A6+1>FIN.MES($A$5;0);MES(A6+1)<>MES($A$5));"";A6+1)
lo más importante en este caso radica en discrimar todas aquellas fechas que no correspondan al mes de estudio, para lo que hemos dado como prueba lógica una función O
O(A6+1>FIN.MES($A$5;0);MES(A6+1)<>MES($A$5))
si la fecha anterior incrementada en un día es mayor al último día del mes analizado o el mes de la fecha difiere del mes de estudio; entonces aplicaremos un condicional SI
=SI(prueba lógica con O;"";fecha anterior + 1)
Como aclaración diré que la función FIN.MES(fecha; meses) nos devuelve la última fecha del mes solicitado, anterior o posterior a la fecha del número de mes indicado; en nuestro caso, al necesitar la fecha de fin de mes del mismo en que nos encontramos le asignamos un valor 0.
Obtenemos como resultado que los valores de fecha que no correspondan al mes de estudio aparecerán sin valor, que es lo que se pretendía.
Para aportar algo más de valor añadido al informe, en una columna adyacente a las fechas del mes, se incluye el día de la semana de cada fecha, empleando una función ya vista DIASEM
=SI(A7="";"";ELEGIR(DIASEM(A7;2);"Lun";"Mar";"Mie";"Jue";"Vie";"Sab";"Dom"))
para todas las celdas de la columna. Además podemos mediante un formato condicional colorear aquellas fechas que caigan en Sábado o Domingo:
El resultado lo podemos ver en el archivo adjunto.
 |
| Fecha FINMES.xls |
| Hosted by eSnips |
Necesito automatizar las fechas de vencimiento de cada cobro que son fechas iguales durante todo el año por cada persona es decir como ejemplo si se inicia un credito el 02/01/2010 se vencera el 02/02/2010
ResponderEliminarPara automatizar las fechas de vencimiento te propongo un par de soluciones, una es empleando la función BUSCARV tal como te muestro en el siguiente enlace:
ResponderEliminarhttp://excelforo.blogspot.com/2010/01/calculo-de-vencimientos-con-la-funcion.html
Otra solución sería con el uso de la función FECHA.MES, de la que subiré un ejemplo en los próximos días.
Un saludo
buenas tardes,
ResponderEliminarsoy bastante novato en el uso de excel, sin embargo estoy tratando de crear una tabla que me permita calcular lo intereses de mora causados por una deuda desde un rando de fechas. las tasas son variables mes a mes por tanto la liquidacion debe hacerse por dias.
qusiera saber si hay una formula que me permita dado el rando de fechas, saber cuantos dias han pasado por cada mes, es decir si le doy un rando del 01-nov-09 hasta el 1-ene-10 me deberia reflejar 30 dias en nov, 31 en diciembre y 1 en enero. es posible hacer esto?
Hola Jorge,
ResponderEliminarhe subido al blog la siguiente entrada donde explico una manera de calcular los días de acuerdo a tu consulta
http://excelforo.blogspot.com/2010/01/calculo-de-diferencias-de-dias.html
Espero que te sirva.
Un saludo