AÑADIR A UNA FECHA AÑOS, MESES Y DÍAS UTILIZANDO DATEADD O DATESERIAL EN VBA

Publicado el 18 mayo, 2019 por Segu

Hola a todos!, qué tal estáis?

Como continuación al post anterior en el que comentaba cómo se pueden crear fechas utilizando la función DateSerial, en el post de hoy vamos a continuar trabajando con esta función pero para realizar otra tarea, es decir, para añadir o (restar) años, meses y días a una fecha.

Efectivamente, con esta función vamos a poder realizar nuestro objetivo. Imaginad que, como indicamos en el post anterior, hemos creado unas fechas a partir de las columnas de año, mes y día y resulta que vamos a utilizar la columna E para añadir o restar días, meses o años:

Para realizar esto, lo vamos a poder hacer con DateSerial y también con otra función DateAdd (os dejo los enlaces a Microsoft).

Por lo tanto, vamos a crear dos columnas para los días, meses y año, una para cada función:

Aquí os dejo la macro que realiza nuestro trabajo:

Option Explicit Sub AÑADE() 'Declaramos variables Dim Fin As Long, i As Long With Sheets("Hoja1") Fin = Application.CountA(.Range("A:A")) 'con un for vamos realizando los cálculos For i = 2 To Fin 'Componemos fecha con dateserial .Cells(i, 4) = DateSerial(.Cells(i, 1), .Cells(i, 2), .Cells(i, 3)) 'Añadimos días don dateserial y dateadd .Cells(i, 6) = DateSerial(Year(.Cells(i, 4)), Month(.Cells(i, 4)), Day(.Cells(i, 4)) + .Cells(i, 5)) .Cells(i, 7) = DateAdd("D", .Cells(i, 5), .Cells(i, 4)) 'Añadimos meses don dateserial y dateadd .Cells(i, 8) = DateSerial(Year(.Cells(i, 4)), Month(.Cells(i, 4)) + .Cells(i, 5), Day(.Cells(i, 4))) .Cells(i, 9) = DateAdd("M", .Cells(i, 5), .Cells(i, 4)) 'Añadimos años don dateserial y dateadd .Cells(i, 10) = DateSerial(Year(.Cells(i, 4)) + .Cells(i, 5), Month(.Cells(i, 4)), Day(.Cells(i, 4))) .Cells(i, 11) = DateAdd("YYYY", .Cells(i, 5), .Cells(i, 4)) Next i End With End Sub

Como podéis observar las dos funciones nos sirven para añadir o restar días, meses o años a nuestra fecha inicial.

Sin embargo, si bien con la función DateSerial podemos sumar en la misma sentencia los días, los meses y los años, (en este ejemplo lo hago por separado):

.Cells(i, 6) = DateSerial(Year(.Cells(i, 4)), Month(.Cells(i, 4)), Day(.Cells(i, 4)) + .Cells(i, 5))

Es decir, podríamos sumar al mismo tiempo los años o los meses. Y esto lo podríamos hacer también con DateAdd, pero programando: AÑADIR AÑOS, MESES Y DÍAS A UNA FECHA EN VBA

Aunque para este ejemplo, me gustaría tratar el comportamiento de ambas funciones por separado.

En caso de Dateserial, basta con sumar a la función que corresponda los días, los meses o los años.

En al caso de DateAdd debemos especificar si se trata de Años (“YYYY”), meses (“M”) o días “D”.

Como podéis ver en el resultado de la macro, el resultado es idéntico en ambos casos, pero existe una diferencia. Esta la vamos a encontrar en los años bisiestos.

AÑADIR A UNA FECHA AÑOS, MESES Y DÍAS UTILIZANDO DATEADD O DATESERIAL EN VBA_2

Por ejemplo, para la fecha 29/02/2016 que fue año bisiesto, donde Febrero tuvo 29 días, ambas funciones ofrecen el mismo resultado cuando aplicamos a días y meses, pero en el caso de añadir años, la cosa cambia:

Por ejemplo, si añadimos a esa fecha 1 año, el resultado con DateSerial es 01/03/2017 mientras que con DateAdd es: 28/02/2017 ¿cuál es el correcto?

Si restamos 1 año, con DateSerial el resultado es: 01/03/2015, mientras que con DateAdd es: 28/02/2015 ¿cuál es el correcto?

Si añadimos 365 años, el resultado es 01/03/2381 con DateSerial y 28/02/2381 con DateAdd. ¿Cuál es el correcto?.

Probablemente no se pueda hablar de correcto o incorrecto, sino adecuado y no adecuado. Y en cualquier caso, el resultado que sería más lógico es el que nos devuelve la función DateSerial, es decir el día 1 del mes siguiente, dado que si no se trata de año bisiesto el día 29 de febrero no existe y por lo tanto equivaldría al 1 de marzo.

Sin embargo es más una cuestión de criterio que de resultado, aunque me parece interesante indicarlo en este post y ponerlo de manifiesto para que lo tengáis en cuenta en vuestros cálculos a la hora de añadir años a una fecha y las funciones a emplear.

Descarga el archivo de ejemplo pulsando en: AÑADIR A UNA FECHA AÑOS, MESES Y DÍAS UTILIZANDO DATEADD O DATESERIAL EN VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

CREAR UNA FECHA CON DATESERIAL EN VBA

Publicado el 17 mayo, 2019 por Segu

Hola a todos:

En muchas ocasiones cuando estamos programando en VBA necesitamos crear o componer fechas a partir de columnas independientes en las que se guardan años, meses y días, por ejemplo:

Esto lo podemos hacer de dos formas, o bien componemos la fecha y aplicamos formato fecha con la función CDate o bien lo hacemos con DateSerial:

En este código ejecutaremos las dos formas:

Option Explicit Sub CREAR_FECHA() 'Declaramos variables Dim Fin As Long, i As Long With Sheets("Hoja1") Fin = Application.CountA(.Range("A:A")) 'con un for vamos realizando los cálculos For i = 2 To Fin 'Componemos fecha y formateamos con Cdate .Cells(i, 4) = CDate(.Cells(i, 1) & "/" & .Cells(i, 2) & "/" & .Cells(i, 3)) 'Componemos fecha con dateserial .Cells(i, 5) = DateSerial(.Cells(i, 1), .Cells(i, 2), .Cells(i, 3)) Next i End With End Sub

Aunque en la práctica el resultado será el mismo, siempre es interesante realizarlo con DateSerial, dado que nos permitirá posteriormente realizar modificaciones en las fechas, como añadir o restar días, meses o años, además de ser más eficiente que la primera.

La función DateSerial, compone la fecha indicando (Año, Mes, Día).

El resultado es el mismo:

Y eso es todo, espero que os haya resultado útil.

Descarga el archivo de ejemplo pulsando en: CREAR UNA FECHA CON DATESERIAL EN VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

FILTRAR POR AÑO EN UN FILTRO CON VBA

Publicado el 12 mayo, 2019 por Segu

Hola a todos!!

Hace unos días publiqué la forma de filtrar por meses utilizando el filtro de Excel con VBA: FILTRAR POR MES EN UN FILTRO CON VBA

Pues bien, hace un par de días un lector me preguntaba cómo podría filtrar pero por años en lugar de meses. La respuesta es que lo podemos hacer, siempre y cuando las opciones del filtro dinámico no nos sean de utilidad (os recomiendo leer el post anterior), dado que podemos filtrar por el año actual, el próximo o el anterior.

Pero si lo que queremos es obtener es todas la fechas en un determinado año, el que sea, entonces lo debemos realizar de forma distinta.

Vamos a emplear la siguiente macro, es importante indicar que para este ejemplo, a diferencia del anterior voy a utilizar un evento de hoja cuando se modifique el contenido de una celda, en la que vamos a especificar el año:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iDate As Date, fDate As Date, Año As Variant Dim fin As Long With Worksheets("Hoja1") 'Si modificamos valor en celda F1, entonces ejecutamos código If Target.Address = "$F$1" Then Año = .Range("F1") 'Si la F1 está vacío o no es numérico mostramos 'todos los datos y salimos del proceso If Año = vbNullString Or Not IsNumeric(Año) Then AutoFilter.ShowAllData Exit Sub End If 'Capturamos inicio y fin del año indicado iDate = DateSerial(Año, 1, 1) fDate = DateSerial(Año, 12, 31) 'Filtramos por todas las fechas de ese año fin = Application.CountA(.Range("A:A")) .Range("$A$1:$A$" & fin).AutoFilter Field:=1, _ Criteria1:=">=" & iDate, Operator:=xlAnd, Criteria2:="<" & fDate + 1 End If End With End Sub

Imaginad estas fechas:

Si aplicamos la macro y en la celda “F1” ponemos, por ejemplo: 2014, se mostrarán todas las fechas que contengan ese año:

Tal y como podéis ver en el código, dato que no disponemos de constantes para los años (sería imposible), tenemos que hacerlo construyendo nuestro propio rango de fechas, es decir, del 01/01/ (y el año que indiquemos) y 31/12/(y el año que indiquemos):

iDate = DateSerial(Año, 1, 1) fDate = DateSerial(Año, 12, 31)

Y con estas dos variables podemos incluirlas en los criterios de nuestro filtro:

.Range("$A$1:$A$" & fin).AutoFilter Field:=1, _ Criteria1:=">=" & iDate, Operator:=xlAnd, Criteria2:="<" & fDate + 1

Es importante hacer notar que el +1 del final es para indicar que la fecha sea menor que 01/01/ (año siguiente al indicado) para conseguir el 31/12/(del año indicado). EL motivo es que el <= no funciona como criterio2 (no he conseguido saber el motivo, pero lo averiguaré).

El resto de la macro sirve para controlar que los datos introducidos no sean alfanuméricos o vacíos (si lo son, se muestran todos los datos). Y para ejecutar la macro si la celda F1 se modifica.

Y eso es todo, espero que os haya resultado de interés y que la duda quede resuelta.

Descarga el archivo de ejemplo pulsando en: FILTRAR POR AÑO EN UN FILTRO CON VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

GENERAR SERIE NUMÉRICA CON BUCLES ANIDADOS Y MATRICES. NÚMEROS NATURALES

Publicado el 4 mayo, 2019 por Segu

Hola a todos:

En muchas ocasiones necesitamos generar una serie numérica con los números naturales, es decir: 1, 2, 3, 4 …hasta infinito.

Existen muchas formas de hacerlo, hoy os propongo hacerlo con un bucle anidado y una matriz en la que almacenar los números generados.

Así sería una forma de hacerlo:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Definimos variables Dim fin As Long, i As Long, celda As String, MiRango As Variant 'Generamos final del rango p. e. de 1 a 25 fin = Application.WorksheetFunction.RandBetween(1, 25) 'Con un for Anidado, incluimos la serie numérica en el array ReDim MiRango(1 To fin) For i = 1 To fin: MiRango(i) = i Next i 'Capturamos celda en la que nos encontramos celda = ActiveCell.Address 'pasamos la serie a la hoja transponiendo los datos en vertical. Range(celda).Resize(fin, 1).Value = Application.Transpose(MiRango) End Sub

Este código lo creado para generar la serie numérica en un rango aleatorio del 1 a la variable “fin” (aleatoria) y se ejecutará cada vez que hagamos doble clic en la celda en la que nos encontremos.

Por ejemplo:

Pero no solo es útil para generar este tipo de series, también podríamos generar series por múltiplos, por ejemplo así: MiRango(i) = i + i obtendríamos esto:

Y por poner otro ejemplo, con los cuadrados MiRango(i) = i ^ 2

Como podéis observar podemos crear prácticamente las series que necesitemos. Es un método muy sencillo y rápido.

Lo importante de esta macro es el método, simplemente pasamos la información al array (MiRango) después de generarla en el For.

ReDim MiRango(1 To fin) For i = 1 To fin: MiRango(i) = i Next i

El resto de la macro es necesaria para realizar el ejemplo y mostrar la información en la hoja.

Descarga el archivo de ejemplo pulsando en: GENERAR SERIE NUMÉRICA CON BUCLES ANIDADOS Y MATRICES. NÚMEROS NATURALES

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

FILTRAR POR MES EN UN FILTRO CON VBA

Publicado el 27 abril, 2019 por Segu

Hola a todos:

Hace unas semanas recibí la siguiente consulta:

“ … tengo una duda: necesito seleccionar de una base datos (p.e. facturación) todos los datos de un mes en concreto de entre varios años sea del año que sea para pasarlos a otra hoja y poder tratarlos. Automáticamente me da el mes como un array (con las fechas de la selección). ¿Cómo puedo decirle en el array el mes “mayo” por ejemplo?”

Y es que aunque parece algo sencillo, el tema de las fechas y el filtro tiene su complicación. Se trata de seleccionar el mes, no una fecha concreta, para lo cual sí podemos utilizar la grabadora de macros y modificar la fecha que necesitamos.

La solución está en trabajar con filtros dinámicos, de esta forma podremos implementar toda una serie de criterios para obtener no solo el mes, sino más elementos.

Por lo tanto, el operador será el filtro dinámico: Operator:=xlFilterDynamic y con él tendremos los siguientes criterios aplicables:

En este enlace tenéis más información: Criterios

Para este post pondré un ejemplo de una posible funcionalidad, imaginad que tenemos una serie de fechas y las queremos filtros a través del mes que aparezca en un Combobox:

Pues bien, aquí os dejo el código completo con el que vamos a realizar el trabajo:

Private Sub ComboBox1_Change() 'Declaramos variables Dim MiArray As Variant, nMes As String, Criterio As Variant, fin As Long 'Creamos matriz de meses MiArray = Array("Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre", " ") 'Cargamos meses en Combobox Sheets("Hoja1").ComboBox1.List = MiArray 'Con una instrucción select case 'pasamos a la variable Criterio la constante que corresponda nMes = ComboBox1.Value Select Case nMes Case "Enero" Criterio = xlFilterAllDatesInPeriodJanuary Case "Febrero" Criterio = xlFilterAllDatesInPeriodFebruray Case "Marzo" Criterio = xlFilterAllDatesInPeriodMarch Case "Abril" Criterio = xlFilterAllDatesInPeriodApril Case "Mayo" Criterio = xlFilterAllDatesInPeriodMay Case "Junio" Criterio = xlFilterAllDatesInPeriodJune Case "Julio" Criterio = xlFilterAllDatesInPeriodJuly Case "Agosto" Criterio = xlFilterAllDatesInPeriodAugust Case "Septiembre" Criterio = xlFilterAllDatesInPeriodSeptember Case "Octubre" Criterio = xlFilterAllDatesInPeriodOctober Case "Noviembre" Criterio = xlFilterAllDatesInPeriodNovember Case "Diciembre" Criterio = xlFilterAllDatesInPeriodDecember End Select 'Filtramos por el mes seleccionado With Worksheets("Hoja1") fin = Application.CountA(.Range("A:A")) .Select 'Si el criterio está vacío, mostramos todos los datos 'y salimos de la rutina If Criterio = vbNullString Then AutoFilter.ShowAllData Exit Sub End If .Range("$A$1:$A$" & fin).AutoFilter Field:=1, _ Criteria1:=Criterio, Operator:=xlFilterDynamic End With End Sub

Para no dejar en la hoja el listado de los meses y luego cargarlos en el combobox, los he incluido en un array en el código.

Luego con un select case le damos al mes seleccionado en el combobox el valor del criterio.

Finalmente filtramos según el criterio seleccionado:

.Range("$A$1:$A$" & fin).AutoFilter Field:=1, _ Criteria1:=Criterio, Operator:=xlFilterDynamic

Por ejemplo, si filtramos por “mayo” obtendremos el siguiente resultado:

y eso es todo, así queda la duda aclarada : )

Espero que os sea de utilidad!
Descarga el archivo de ejemplo pulsando en: FILTRAR POR MES EN UN FILTRO CON VBA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST

Publicado el 21 abril, 2019 por Segu

Hola a todos!:

En esta web hay varios post dedicados a los listbox, tanto en formularios como objetos en la hoja. En el post de hoy me gustaría mostrar un código en el que vamos a ver un método para cargar los datos en nuestro listbox y también para ordenarlos.

Imaginad los siguientes datos:

Por un lado tenemos un listado de nombres y apellidos y por otro de números. Nuestro objetivo es pasar al listbox los datos seleccionados y ordenarlos.

Veamos el código que os propongo:

Sub ORDENAR_LIST() 'Definimos variables Dim MiMatriz As Object, Celda As Variant, nItem As Variant 'Vaciamos listbox Call BORRAR With Sheets("ORDENAR") 'Creamos objeto ArrayList Set MiMatriz = CreateObject("System.Collections.ArrayList") 'Pasamos los datos seleccionados al ArrayList 'Si la celda está vacía, no la tenemos en cuenta 'Formateamos el contenido de cada celda como texto For Each Celda In Selection If Not IsEmpty(Celda) Then MiMatriz.Add CStr((Celda)) Next Celda 'Ordenamos MiMatriz.Sort 'Pasamos la información al listbox For Each nItem In MiMatriz .ListBox1.AddItem (nItem) Next nItem End With End Sub
Sub BORRAR() With Sheets("ORDENAR") 'Limpiamos combo y listbox .ListBox1.Clear End With End Sub

Como podéis observar, vamos a trabajar con la selección como un rango de datos. Para ello tendremos que crear el objeto arraylist que nos permitirá ordenar la información de menor a mayor con el método .Sort.

Básicamente, con una instrucción For – Each vamos pasando cada elemento de la selección a nuestro arraylist (en este ejemplo no tendremos en cuenta las celdas vacías) y además damos formato texto a cada valor de las celdas seleccionadas (CStr) evitando así errores de tipos y también en el método Sort a la hora de comparar los datos para la ordenación.

Una vez pasados los datos, ordenamos. Si quisiéramos invertir el orden, simplemente tendríamos que incluir una nueva línea debajo del .Sort:

MiMatriz.Reverse

Una vez que lo tenemos, solo tenemos que pasar los datos al listbox mediante otro For-Each.

El resultado es para los nombres:

Y para los números:

Como podéis comprobar, el método funciona perfectamente para ambos casos.

Espero que os haya resultado de interés!.

Descarga el archivo de ejemplo pulsando en: ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

ORDENAR VALORES UTILIZANDO FÓRMULAS

Publicado el 14 abril, 2019 por Segu

Hola a todos:

Estos días he recibido multitud de consultas y hoy me gustaría escribir un post muy sencillo sobre la posibilidad de ordenar valores utilizando únicamente fórmulas.

En la consulta inicial me preguntan cómo se pueden ordenar una serie de valores siempre numéricos tanto de menor a mayor como a la inversa, de mayor a menor.

Bien, esto lo podemos hacer con una sencilla fórmula, veamos los datos:

En este caso es una relación de personas con una cifra que ha alcanzado (de lo que sea, nos vale como ejemplo).

La cuestión es ahora cómo ordenar esos números sin utilizar el filtro o el comando de ordenar de la cinta de opciones.

Pues bien, podemos utilizar la siguiente función:

=K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2))

Con K.esimo.Mayor vamos a obtener el número mayor de un rango, en este caso, al mover la fórmula nos va a ir mostrando el mayor según el rango seleccionado.

Y el resultado será el siguiente:

Como podéis ver, conseguir ordenar el rango de Mayor a Menor. Si necesitamos invertirlo y hacerlo de Menor a Mayor, utilizaremos K.esimo.Menor.

Pero el lector me solicitó si también podía conseguir que se mostrase en otra columna los nombres relacionados con los números ordenados. Y en efecto, sí podemos obtenerlos, y lo vamos a hacer con la siguiente fórmula:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR($B$2:B10;FILAS($B$2:B2));$B$2:B10;0)+1;1))

Dado que con la fórmula inicial podemos ordenar los números en relación a la lista inicial, con la función Coincidir podemos obtener la fila en la que se encuentra nuestro número antes de ordenarlo, y si sabemos la fila, con la función Dirección podemos obtener la celda que nos interesa de la columna A para obtener el nombre.

Así:

He indicado con flechas en rojo la dirección con la que obtenemos las celdas de referencia que nos interesa.

Ahora, para obtener el nombre, utilizaremos la función indirecto:

Y así, finalmente obtenemos toda la información que necesitamos.

Bien!!

Pero a los pocos días el mismo lector me solicitó si de alguna manera podría incluir rangos dinámicos en ambas fórmulas para no tener que ir actualizando los rangos a mano cada vez que el listado se fuese incrementando.

Pues eso también se puede realizar, la manera es incluyendo la función Desref en las dos fórmulas, y lo haremos sustituyendo en ambas fórmulas la parte que hace referencia a los rangos por:

DESREF($B$2; 0; 0; CONTARA(B:B))

Y así ya podemos obtener la misma información, pero con rangos dinámicos:

Para ordenar:

=K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA(B:B));FILAS($B$2:B2))

Para obtener los nombres:

=INDIRECTO(DIRECCION(COINCIDIR(K.ESIMO.MAYOR(DESREF($B$2; 0; 0; CONTARA($B:$B));FILAS($B$2:B2));DESREF($B$2; 0; 0; CONTARA($B:$B));0)+1;1);1)

En resumen:

Y eso es todo!. Como podéis observar, lo hemos conseguido.

Es importante comentar que está fórmula es solo válida para datos numéricos y en los que no existen duplicados. Si contamos con duplicados tendríamos que trabajar con una columna auxiliar en la que introducir algún elemento para “desempatar” esos datos, por ejemplo añadiendo una cifra muy pequeña a los números originales.

Descarga el archivo de ejemplo pulsando en: ORDENAR VALORES UTILIZANDO FÓRMULAS

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

¡¡Muchas gracias!!

	Agustin Mustieles en EXPORTAR DATOS DE EXCEL A ACCE…
	francisco jose en GESTIÓN DE CLIENTES CON E…
	francisco jose en GESTIÓN DE CLIENTES CON E…
	Segu en COMBINAR CORRESPONDENCIA Y GUA…
	Segu en COMBINAR CORRESPONDENCIA EN EX…

EXCEL SIGNUM

SI LO IMAGINAS, LO PUEDES CREAR

AÑADIR A UNA FECHA AÑOS, MESES Y DÍAS UTILIZANDO DATEADD O DATESERIAL EN VBA

CREAR UNA FECHA CON DATESERIAL EN VBA

FILTRAR POR AÑO EN UN FILTRO CON VBA

GENERAR SERIE NUMÉRICA CON BUCLES ANIDADOS Y MATRICES. NÚMEROS NATURALES

FILTRAR POR MES EN UN FILTRO CON VBA

ORDENAR ELEMENTOS DE UN LISTBOX UTILIZANDO OBJETO ARRAYLIST

ORDENAR VALORES UTILIZANDO FÓRMULAS

SI LO IMAGINAS, LO PUEDES CREAR

Compártelo:

Me gusta:

Compártelo:

Me gusta:

Compártelo:

Me gusta:

Compártelo:

Me gusta:

Compártelo:

Me gusta:

Compártelo:

Me gusta:

Compártelo:

Me gusta: