lunes, diciembre 22, 2008

Insertar un calendario permanente en Excel 2007.

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 comentarios:

  1. 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

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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

    ResponderBorrar
  4. 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.

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. 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!!

    ResponderBorrar
  7. 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.

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

    ResponderBorrar
  9. 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"

    ResponderBorrar
  10. 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.

    ResponderBorrar
  11. Se puede hacer usando Formato Condicional.

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

    ResponderBorrar
  13. borra los ; y sustituyelos por ,

    ResponderBorrar
  14. 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)

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

    ResponderBorrar
  16. 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

    ResponderBorrar
  17. 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.

    ResponderBorrar
  18. 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.

    ResponderBorrar
  19. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  20. 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.

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

    ResponderBorrar
  22. 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.

    ResponderBorrar
  23. 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

    ResponderBorrar
  24. 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).

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

    ResponderBorrar
  26. 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).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.