Insertar un calendario permanente en Excel 2007.

lunes, diciembre 22, 2008

Hace ya más de dos años publiqué la nota sobre como insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX. Eran los días previos a la aparición de Excel 2007 y los controles estaban ligados a dos barras de herramientas, Formularios y Cuadros de Controles (controles ActiveX).
En Excel 2007 no hay barras de herramientas y los controles están ubicados en la pestaña Programador de la cinta de opciones




Nótese que todas las herramientas necesarias están a nuestra vista en la misma sección de la pestaña (Modo diseño, Propiedades, etc.).

Como pueden ver, al apretar Insertar vemos de hecho las mismas barras de herramientas a las que estábamos acostumbrados en las versiones anteriores de Excel.

También el modelo de validación de fechas programando un evento que muestre el calendario si se cumplen ciertas condiciones podemos usarlo en Excel 2007. Si bien la interfaz del editor de Visual Basic no ha cambiado algunos lectores me comentan que no encuentran el botón de controles adicionales. Al igual que en las versiones anteriores podemos usar el menú Herramientas-Controles Adicionales o hacer un clic con el botón derecho del mouse en cuadro de herramientas del UserForm



También podemos crear un calendario permanente en una hoja de Excel sin usar controles. Esto puede hacerse con fórmulas matriciales, como en este modelo desarrollado por John Walkenbach



Este modelo usa fórmulas matriciales de rango, es decir una fórmula que da el resultado en varias celdas simultáneamente. Para crear este modelo empezamos por introducir en la celda B2 el mes y el año del calendario. Podemos hacer esto poniendo en B2 la fórmula =HOY() y dando un formato “mmmm,aaaa” a la celda. Luego seleccionamos el rango B4:H9 asegurándonos que la celda activa sea B4, introducimos esta fórmula en la celda activa

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1);"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1)

y apretamos simultáneamente Ctrl+Mayúsculas+Enter

Podemos usar una fórmula más sencilla,

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-1)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

pero que mostrará las fechas en todas las casillas del rectángulo



Como ven, estos calendarios son “gringos” con la semana comenzando en domingo. Si queremos que la primer columna de la semana sea el lunes, modificamos levemente la fórmula

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7});"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7})



O esta fórmula

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-2)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

para el modelo sencillo







Technorati Tags:

27 comments:

Anónimo,  23 diciembre, 2008 02:08  

Estimado Jorge, tengo un inconveniente el cual seguramente obedecerá a mi escaso conocimiento sobre Excel.
He tratado de armar un calendario aplicando la fórmula para que el calendario comience en Lunes (NO la más sencilla) y en lugar de aparecerme los números de los respectivos días, me aparece una serie de números: comenzando por el 39783 (debería ser Lunes 1) y finalizando con el 39813 (debería ser el Miércoles 31).
¿Tendré algún problema de formato? Probé con formatos General, Número y Personalizado (0 y 00).
Agradeceré si me puedes orientar.
Muchas gracias
Carlos

Jorge L. Dunkelman 23 diciembre, 2008 21:12  

Hola
culpa mía, mil disculpas. Me olvide de señalar que las celdas que contienen las fechas tiene el formato personalizado "d". Cuando ves 37983, Excel interpreta que son 37983 días después de la fecha 01/01/1900, es decir 28/12/2008. Para ver ese número como fecha tenés que aplicar el formado "dd/mm/aa". Al aplicar "d" ves sólo el día.

Anónimo,  24 diciembre, 2008 01:54  

Jorge, tu generosidad y humildad no tiene límites!!!
No sólo ofreces temas interesantes con sus respectivas soluciones, si no que cuando un "inexperto" tiene un problema procedes con total HUMILDAD a responder de esta manera.
Te agradezco mucho!!!
Carlos

Carlos 14 enero, 2009 14:54  

Hola Jorge,
Utilizando las fórmulas de inicio en lunes, he observado que para el mes de Febrero/2009 no contempla el inicio de mes (1) en domingo y se inicia con lunes 2 de Febrero de 2009.
Otro Carlos.

Carlos 14 enero, 2009 17:44  

Hola de nuevo Jorge, soy el otro Carlos.
Despues de analizar tu fórmula, creo que he resuelto el problema expuesto (de los meses que empiezan en domingo p.ej Feb/2009 Mar/2009 Nov/2009 Ago/2010) y es con una pequeña modificación de tu formula (4)
=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{-1\0\1\2\3\4\5\6}*7+{1;2;3;4;5;6;7});"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{-1\0\1\2\3\4\5\6}*7+{1;2;3;4;5;6;7})
Agradecido por tu blog...
saludos.
Carlos

Anónimo,  31 julio, 2010 00:59  

Buenas tardes amigo... de antemano agradeciendo toda la ayuda que nos presta a los novatos de EXCEL.
Mi duda es la siguiente, en este codigo que deberia modificar para solamente visualizar los dias de lunes a viernes(solo habiles)???
Una vez mas, gracias!!

Jorge L. Dunkelman 31 julio, 2010 10:50  

No hay que modificar la fórmula. Sólo debes copiarla a un rango de cinco columnas por cuatro filas. Es decir, creas un almaque que solo muestra los días de lunes a viernes.

Anónimo,  08 agosto, 2010 07:23  

en el ecxel que tengo en la banda del menu no esta la pestaña programador que hago?? gracias
homtobate@hotmail.com

Jorge L. Dunkelman 08 agosto, 2010 14:14  

Pulsar el botón del Office y luego el botón de Opciones de Excel. En la opción Más Frecuentes marcar "mostrar ficha Programador en la cinta de Opciones"

miclasedemusica 14 marzo, 2011 01:17  

saludos, he utilizado tu formula aunque la modifique un poco ya que necesitaba 1°queelcalendario fuera lineal, 2° que solo me calculara martes, jueves y domingo, en las primeras 3 celdas me funciono, pero luego no calculaba, por lo que volvi a modificar la formula.
Ahora mi consulta pudiese modificarla de tal manera que si es un dia con fecha anterior o posterior a la fecha o mes principal que esta apareciera tachada o de color.
gracias de antemano por tu colaboracion tan especial para tus lectores.

Jorge L. Dunkelman 14 marzo, 2011 06:31  

Se puede hacer usando Formato Condicional.

Anónimo,  02 agosto, 2011 04:10  

Que tal, seguí las instrucciones y excel marca constantemente un error.
No acepta la formula.
Espero me puedan ayudar.

Jorge L. Dunkelman 02 agosto, 2011 07:01  

¿Que dice el mensaje del error?

Anónimo,  16 septiembre, 2011 19:39  

borra los ; y sustituyelos por ,

Cacho 04 agosto, 2012 15:57  

me sale error en tu primer formula: =SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1);"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1)

Jorge L. Dunkelman 06 agosto, 2012 18:54  

Asegurate de introducir la fórmula en forma matricial (Ctrl+Mayúsculas+Enter).

Jaime Aya 11 junio, 2013 19:30  

buenas tardes jorge, como puedo obtener un calendario al pararme en una celda, y poder escoger una fecha de ese calendario y que salga en la celda. Gracias

Jorge Dunkelman 11 junio, 2013 22:31  

Hola Jaime, en el blog hay varias notas sobre el tema a las que se puede acceder usando el enlace "Calendario" en la nube de etiquetas (en la parte superior del blog).
Esta es la primer nota que publiqué y es una actualización para los usuarios de Excel 2010.

Anónimo,  21 agosto, 2015 17:02  

Jorge, como puedo utilizar esta formula en una sola columna, omitiendo sábados y domingos? Si es posible me facilitaría muchas cosas. Desde ya muchas gracias.

Jorge Dunkelman 22 agosto, 2015 20:04  
Este comentario ha sido eliminado por el autor.
Jorge Dunkelman 23 agosto, 2015 20:56  

Existe la posibilida de hacerlo con facilidad usando la funcionallidad Inicio-Modificar-Rellenar-Series.
En los próximos días estaré publicando un post sobre el tema.

Unknown 21 septiembre, 2015 19:39  

Hola Jorge, buenas tardes como podría hacer para que las fechas salieran en horizontal

Jorge Dunkelman 22 septiembre, 2015 07:34  

Para que todas las fechas del mes salgan en una fila hay que modificar la fórmula. Con el modelo sencillo (muestra siempre 31 días) la fórmula es

=FECHA(AÑO(B13),MES(B13),1)-(DIASEM(FECHA(AÑO(B13),MES(B13),1))-2)+COLUMNA(1:31)-1

aplicada matricialemente, como se explica en el post.

Hermes Jose Garcia Ramirez 22 septiembre, 2015 16:03  

Buenos Días Jorge, en efecto los muestra pero me arrojo de otro mes, y si deseo que inicie con el domingo y finalice el sábado cual sería la formula.
Muchas gracias por responderme

Jorge Dunkelman 22 septiembre, 2015 18:30  

En ese caso hay que adaptar la fórmula más compleja de la siguiente manera:

=SI(MES(FECHA(AÑO(B2),MES(B2),1))<>MES(FECHA(AÑO(B2),MES(B2),1)-(DIASEM(FECHA(AÑO(B2),MES(B2),1))-1)+(COLUMNA(1:36)-1)),"",FECHA(AÑO(B2),MES(B2),1)-(DIASEM(FECHA(AÑO(B2),MES(B2),1))-1)+(COLUMNA(1:36)-1))

y aplicarla matricialmente a un rango horizontal de 35 celdas (para cubrir todas las posibilidades del primer día del mes).

Begoña 24 febrero, 2016 18:49  

Buenas, por que el calendario me queda en columnas y no en filas.

Jorge Dunkelman 25 febrero, 2016 07:07  

Begoña, tendrías que mandarme tu hoja para ver lo que estás haciendo. Fijate en las instrucciones en el enlace Ayuda (en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP