Mostrando las entradas con la etiqueta Calendario. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Calendario. Mostrar todas las entradas

jueves, febrero 27, 2020

Calendario con horas en Power Query

Ya he mencionado que mi abuelita solía decir que cuando tenemos un martillo todo problema se parece a un clavo. Power Query es mi "martillo" a la hora de resolver un problema de transformación de datos en Excel.

El "clavo" de turno era crear un calendario, similar al del post anterior, pero con horas. Es decir un fila para cada hora de cada día. En lugar de 365 o 366 filas, 8760 o 8784 si el año es bisiesto.

El método es similar al del post mencionado pero con ciertos cambios. Como queremos que nuestra solución sea dinámica, empezamos por definir un conexión a una celda donde ponemos el año para el cual queremos crear el calendario



Esta consulta la guardamos como "solo conexión" (ver el post anterior). El código es

martes, febrero 25, 2020

Crear un calendario con Power Query

Supongamos que queremos crear un calendario con Power Query (¿por qué queríamos hacer semejante cosa? Bien, hay varios motivos pero no son el tema de este post).

Aparentemente la tarea es bien sencilla. Power Query tiene un "atajo" para crear Listas: {x..y} donde x e y son números enteros. Como sabemos, las fechas están representadas por números enteros de manera que si sabemos que el 01/01/2020 es el número 43831 y el 44196 el 31/12/2020, hacemos lo siguiente:

1 - abrimos una consulta en blanco

2 - en la barra de las fórmulas escribimos ={43831..44196} lo cual crea un serie aritmética de razón 1


lunes, enero 21, 2013

Una alternativa al control Calendario en Excel 2010 – Monthview

Como mencionamos en el pasado el control Calendar (mscal.ocx) fue removido del paquete Office 2010. Esto genera problemas en aplicaciones y modelos de Excel que fueron desarrollados en versiones anteriores y queremos usarla en Excel 2010.

En la nota mencionada sugerimos tres alternativas posibles: usar el Data Picker, el complemento desarrollado por Sam Radakovitz o descargar e instalar el control. Esta última alternativa implica tener que registrar el control para poder usarlo. El problema con esta última solución es que no podemos registrar el control en Windows 8.

Para los que, como a mí, no les gusta el Data Picker, existe la posibilidad de usar el control Monthview (para las versiones 2007 y 2010 de Excel).

¿Cuándo queremos usar el control? Cuando necesitamos asegurarnos que el usuario ingrese fechas en el rango y en el formato necesarios, librándolo al mismo tiempo de tener que ingresar los separadores (”/” o “-“) de la fecha.

En esta nota mostraré como implementar un modelo sencillo: al elegir una celda determinada aparece el calendario y al elegir una fecha en él, ésta aparece en la celda y el calendario desaparece



Paso 1 – Crear un Userform

En el editor de VB (Alt+F11) creamos un userform que contendrá el control



Cambiamos la propiedad Name a “UfFecha” (no es indispensable pero es una buena práctica) y cambiamos Caption a “Elija una fecha”.

Paso 2 - Insertar el control Monthview

Apuntamos al cuadro ToolBox y abrimos el menú contextual con un clic en el botón derecho del mouse



Elegimos al opción Aditional Controls y marcamos el Microsoft Monthview Control



Este aparecerá ahora en la parte inferior del cuadro ToolBox. Con un clic lo seleccionamos y lo insertamos en el Userform



Paso 3 – Definiciones del control

Al seleccionar el control podemos ver el cuadro de propiedades en el editor. Esto nos permite definir su apariencia y comportamiento. Por ejemplo, definimos la propiedad ShowTodat como True, de manera que al abrirse el control muestre la fecha corriente.



Paso 4 – Códigos de los controles.

Ahora necesitamos escribir los códigos que manejen el uso del control. En nuestro modelo queremos que cuando el usuario seleccione la celda C3 (hemos creado un nombre definido que se refiere a la celda: “clFecha”) aparezca el calendario. Esto lo hacemos usando el evento Worksheet_SelectionChange de la hoja

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("clFecha")).Address = Range("clFecha").Address Then
        ufFecha.Show
    End If

End Sub


El código lo ponemos en el módulo de la hoja (abrimos el módulo de la hoja con la opción Ver Código del menú contextual que se abre al hacer clic derecho en la pestaña de la hoja o con un doble clic sobre el icono de la hoja en el editor de Vb)



Ahora necesitamos un código para pasar el valor elegido en el calendario a la celda

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    ActiveCell.Value = DateClicked
    Unload ufFecha
End Sub


Este código va en el módulo del Userform que se abre haciendo un doble clic en el Userform (o apretando Ctrl+Tab hasta llegar al módulo)

Para que el control aparezca junto a la celda de la fecha y no en el centro de la hoja, agregamos el evento UserForm_Activate

Private Sub UserForm_Activate()
    With Me
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 150
    End With
End Sub


El modulo del Userform se verá así



Un tip para el final: si hacemos unn clic sobrel el nombre del mes en el control aparecerán unas flechas que nos permiten saltar de año en año



El archivo con el ejemplo puede descargarse aquí.

jueves, diciembre 22, 2011

Ajuste automático de fecha en el calendario de Excel

Una de las notas más populares de este blog es Validar fechas en Excel con un calendario. Al presente registra más de 33 mil vistas y más de 130 comentarios.

Microsoft decidió retirar el control Calendario del paquete de Office 2010, pero muchos de mis lectores siguen usando versiones anteriores o han instalado el control independientemente.

Una de las consultas que recibo en relación a esa nota es cómo hacer para que el calendario se abra en la fecha corriente. Uno de los lectores puso en un comentario cómo hacerlo, pero por lo general los nuevos lectores no se detienen a leer todos los comentarios. Por ese motivo, mostraré en esta nota los pasos a dar para lograr ese efecto.

Creamos el userform con el control Calendario y ponemos los códigos de los eventos, tal como mostré en la nota mencionada.

Ahora agregamos un evento para establecer la fecha del calendario. En el editor de Vba seleccionamos el userform



apretamos F7 para abrir el módulo del control y agregamos este código al evento Activate del Userform

Private Sub UserForm_Activate()
    Calendar1.Value = Now
End Sub




Este evento hará que el calendario se abra siempre en la fecha del día corriente.

miércoles, marzo 16, 2011

Usar el control Calendario en Excel 2010

En el pasado, en aquellos lejanos días de Excel Clásico (97-2003), mostramos en este blog las bondades de usar el control Calendario (Mscal.ocx) y también como validar fechas con el control.

Las malas noticias para los que empiezan a trabajar con Excel 2010 es que el control ha sido removido del paquete de Office.

Microsoft comenta el tema en esta nota diciendo:

El control Calendar (mscal.ocx) […] fue removido de Access 2010 y no se puede utilizar en Excel 2010. En cambio, los usuarios pueden utilizar el Date Picker o sus propios controles de calendario personalizado.

El Date Picker se encuentra en la colección de controles ActiveX



Una segunda posibilidad es usar controles o complementos de terceras partes. Personalmente puedo recomendar el complemento desarrollado por Sam Radakovitz. Todo la que hay que hacer es descargar el complemento en la nota que Sam publicó el blog de Microsoft Office y copiarlo en la carpeta XLSTART (por ejemplo: C:\Program Files\Microsoft Office\OFFICE14\XLSTART).

Una vez instalado el complemento aparecerá en la cinta en la pestaña Complementos



El complemento hace que el calendario aparezca en toda celda que contenga una fecha o en la celda inmediata inferior.
También aparece en la celda que se encuentre debajo de una que contenga la palabra “Date” (fecha, en inglés. No, no funciona con Fecha en castellano) o desde el menú contextual que se abre con un clic del botón derecho del mouse



Existe una tercera posibilidad: instalar el control Calendario de Excel Clásico (Mscal.ocx) para utilizarlo en Excel 2010. Esta posibilidad es válida para la versión 32 Bit de Excel 2010.

El control puede descargarse en fonstuff o mejor aún en la página de descargas de Graham Mayor (buscar el MSCAL.ZIP).

Para poder usar el control hay que copiar el archivo MSCAL.OCX en C:\Windows\System32 y registrarlo. Para registrarlo hacemos un clic a Start (Inicio) y en Run copiamos “regsvr32 mscal.ocx” en



Apretamos OK y veremos esta mensaje



Ahora el control aparecerá en la colección de controles ActiveX

sábado, abril 03, 2010

Posición del objeto Calendario en una hoja de Excel

De vuelta mis vacaciones estoy tratando de ponerme al día con las consultas de mis lectores. Lamentablemente no podré responder a todas, pero algunas serán transformadas en notas en el blog. Como la consulta de un lector sobre cómo posicionar el objeto Calendario en una hoja de Excel.

La consulta viene a colación de la nota sobre validación de fechas en Excel que publiqué hace ya casi tres años. Allí mostré como hacer que el objeto Calendario aparezca cuando activamos una celda de un determinado rango.

La consulta de mi lector consiste en cómo lograr que el calendario aparezca siempre a la derecha de la celda activa y no en el centro del área de la hoja que aparece en pantalla. Para lograr esto tendremos que programar la propiedad Position del Userform que contiene el calendario.

Empezamos por descargar el ejemplo de la nota mencionada. Luego activamos el editor de Vba (Alt+F11) y el Userform1. En la ventanilla de las propiedades activamos la pestaña Categorized y vamos al grupo Position



Vemos que tenemos, además del tamaño (Height y Width) la posibilidad de establecer la posición inicial (StartUpPosition)



En nuestro caso vamos a usar la opción 0-Manual. Por supuesto, esto no es suficiente y tendremos que establecer la posición relativa a la celda activa programáticamente.

Activamos el Modulo1 que contiene la rutina "abrir_calendario" y ponemos este código



Sub abrir_calendario()
    With UserForm1
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 75
        .Show
     End With
    
End Sub


El código es bastante obvio y se puede apreciar cómo se controla la posición en relación a la celda activa. Los parámetros "+25" y "+75" nos permiten establecer el grado de desvío.

El archivo se puede descargar gratuitamente aquí.

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:

viernes, mayo 18, 2007

Validar fechas en Excel con un calendario

A partir de la nota sobre cómo insertar un calendario en una hoja de Excel, un lector me pregunta como crear un calendario que aparezca cuando elegimos una celda en la que queremos ingresar una fecha.
El objetivo, claramente, es validar la entrada. Es decir, asegurarnos que el usuario ingrese una fecha y no un número cualquiera.
Una forma práctica de hacerlo es usando Validación de datos. Por ejemplo, si queremos asegurarnos que el rango A2:A20 el usuario ingrese sólo fechas del año 2007, podemos definir esta validación de datos




De esta manera, si el usuario elige una fecha fuera del año 2007, recibirá un mensaje de error



Pero en realidad lo que queremos es que el usuario elija la fecha que quiere introducir en la celda. También esto podemos hacerlo con validación de datos, usando la opción Lista.
Creamos un nombre, año_2007, que contenga las fechas a usar. Para esto podemos, por ejemplo, poner las fechas en el rango A1:A365 de la Hoja2. Luego definimos la validación de datos



y así obtenemos una lista desplegable.



Coincidamos en que estos métodos no son muy flexibles que digamos. Así que nuestro objetivo será crear un calendario que aparezca cada vez que queremos elegir una fecha. Esto la haremos con una macro y el Calendar Control (el número depende de la versión de Excel), que ya mencionamos en nuestra nota anterior.

Nuestra macro puede estar almacenada en el Personal.xls y de esta manera estar disponible para todo cuaderno activo. O puede estar ligada a un cuaderno específico y sólo estará disponible cuando este cuaderno esté abierto. Esto último es lo que mostraremos en esta nota.

Nuestro proyecto se compone de lo siguiente:
- una forma que contenga el calendario
- una macro que muestre el calendario cada vez que la celda activa esté dentro del rango indicado e introduzca la fecha elegida en la celda activa
- un evento que dispare la macro cuando la celda activa esté en el rango indicado.

Empecemos por crear la forma que contenga al calendario. Para esto activamos el editor de Visual Basic (ALT+F11) y agregamos una forma (UserForm) al proyecto



En la ventana de Propiedades el proyecto (si no está visible usamos el menú Ver—Ventana Propiedades del VBe, o pulsamos F4) cambiamos el valor estándar en Caption por "Elija un fecha"



Para agregar el Calendar Control debemos hacer visible el cuadro de herramientas (menú Ver—Cuadro de Herramientas). Inmediatamente usamos el menú Herramientas—Controles Adicionales



Y elegimos el Calendar Control. Esto hace que su icono aparezca en el cuadro de controles a la izquierda de la forma. Pulsamos el icono y creamos el calendario sobre la forma



Las propiedades del calendario pueden ser cambiadas en la ventana de Propiedades, estando este seleccionado.


Ahora tenemos que escribir el código para el calendario. Para esto, con la forma seleccionada, usamos el menú Ver—Código (atajo: F7). Esto nos lleva a la ventana de código de la forma. En ella escribimos



Para probar el funcionamiento de la forma la seleccionamos con Ctrl+Tab y pulsamos F5. La forma aparecerá en la Hoja1. Para cerrarla pulsamos la X en el ángulo superior derecho.

Nuestro próximo paso es poner un código que transfiera la fecha elegida en el calendario a la celda activa en la hoja.
Hacemos un doble clic sobre el calendario en la forma, lo que abre la ventana de código donde escribimos


Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub


Volvemos a probar el funcionamiento de nuestra forma, seleccionándola con Ctrl+Tab y F5 para accionarla. Elegimos una fecha del calendario y veremos que este se cierra y la fecha elegida aparecerá en la celda activa.

Ahora necesitamos un método para que el calendario se abra sólo cuando la celda seleccionada (activa) este comprendida en el rango donde queremos poner las fechas (en nuestro caso A2:A20).
Para esto necesitamos dos elementos: una rutina que haga aparecer la forma y un evento que accione la rutina cuando la celda seleccionada cae en el rango indicado.

La macro para mostrar la forma la escribimos en un módulo de Vba, que agregamos usando el icono o con el menú Insertar—Módulo:


Sub abrir_calendario()
UserForm1.Show
End Sub


El evento debemos escribirlo en la ventana de código de la Hoja1, que abrimos haciendo un doble clic en el icono de la Hoja1 en la ventana del proyecto.

Allí agregamos este código


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngFechas As Range

Set rngFechas = Range("A2:A20")

If Union(Target, rngFechas).Address = rngFechas.Address Then _
Call abrir_calendario


End Sub


Con esto hemos terminado nuestro proyecto. Ahora, cada vez que seleccionemos una celda en el rango A2:A20 de la Hoja1, aparecerá el calendario y podemos elegir la fecha.



Como abran notado, podemos cambiar el año y el mes libremente.





Technorati Tags:

sábado, julio 15, 2006

Insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX

Ayer vimos como usar controles ActiveX en hojas de Excel. Para insertarlos debemos previamente activar la barra de herramientas del Cuadro de Controles. En la esquina inferior derecha de la barra del Cuadro de Controles, está el icono "más controles".

Al activarlo tendremos acceso a muchos más controles (no todos disponibles para las hojas de cálculos). Uno de ellos es el Calendar Control, que nos permite insertar un calendario permanente en la hoja de cálculos.



Para insertar el calendario en una hoja Excel, procedemos como con los otros controles ActiveX, como mostramos en la entrada anterior.

Una vez insertado en la hoja, abrimos el menú de propiedades, para adpatar el calendario a nuestras necesidades

En este calendario_spejemplo de calendario en Excel, tenemos una tabla con ventas diarias en la Hoja2. En la Hoja1 insertamos el calendario, y lo ligamos (LinkedCell) a la celda B7 (que no será visible al estar cubierta por el calendario).

En la celda B14 introducimos esta fórmula, que usa la celda ligada B7 como argumento

=FECHANUMERO(B7)

Esta formula convierte el valor textual del calendario a una fecha. En la celda C14 introducimos una función BUSCARV, para obtener el valor de las ventas correspondientes a la fecha elegida en el calendario

=SI(B14>MAX(Hoja2!A:A);"fecha sin datos";BUSCARV(B14;Hoja2!A:B;2;0))

la condición lógica con la función MAX, nos permite evitar valores erróneos cuando se elige una fecha que no exste en la tabla de ventas.

Ahora, podemos elegir la fecha en el calendario y ver la suma de ventas correspondientes en la celda C14.





Además del menú de propiedades en la barra de herramientas, también se puede cliquear con el boton izquierda el control y abrir este diálogo





Categorías: Varios_

Technorati Tags: ,