MACRO PARA HALLAR DIFERENCIAS ENTRE DOS BASES DE DATOS

Hola a todos:

En el post de hoy voy a tratar sobre una consulta que me ha enviado un lector y que es un tema muy recurrente en el mundo de los profesionales que trabajan con plantillas con la misma información, pero que proceden de orígenes o sistemas distintos.

Como es lógico siempre queda la duda de si la información es la misma en ambos archivos, una fecha distinta, un literal mal escrito, etc, etc.

Pues bien, ante esta problemática se puede abordar la solución utilizando dos métodos, o bien con ADO y SQL o utilizando loops que vayan comparando la información de cada archivo. Para este post utilizaré la segunda solución (y en otra entrada futura comentaré la primera alternativa).

Veamos un ejemplo, imaginad que hemos descargado la siguiente base de datos desde dos fuentes distintas, es decir, que tenemos dos archivos y que deberían ser idénticos:

El primero está en la hoja 1:

MACRO PARA HALLAR DIFERENCIAS ENTRE DOS BASES DE DATOS

El segundo está en la hoja 2:

MACRO PARA HALLAR DIFERENCIAS ENTRE DOS BASES DE DATOS2

Aunque a simple vista ya podéis ver alguna diferencia, con la siguiente macro podréis comprobar los error o diferencias fácilmente:

Sub Compara_Hoja1()
'Declaramos variables
Dim scadena As String, scadena_2 As String
Dim i As Long, j As Long, n As Long
Dim col As Long, fin As Long, final As Long, d As Long
Dim a As Long, x As Long, p As Long, col_1 As Long, col_2 As Long
'Trabajamos con la Hoja1
With Sheets("Hoja1")
fin = Application.CountA(.Range("A:A"))
final = Application.CountA(Sheets("Hoja2").Range("A:A"))
col_1 = .Cells(1, Cells.Columns.Count).End(xlToLeft).Column
col_2 = Sheets("Hoja2").Cells(1, Cells.Columns.Count).End(xlToLeft).Column
'Iniciamos primer loop recorriendo registros hoja1
For i = 2 To fin
'Componemos cadena con toda la fila hoja1
For d = 1 To col_1
scadena = scadena & .Cells(i, d).Value
Next d
'Iniciamos segundo loop buscando valor de scadena de hoja1 en hoja2
For j = 2 To final
'Componemos cadena de toda la fija hoja2
For a = 1 To col_2
scadena_2 = scadena_2 & Sheets("Hoja2").Cells(j, a).Value
Next a
'Si el ID existe en la hoja2 contamos
If .Cells(i, 1) = Sheets("Hoja2").Cells(j, 1) Then p = p + 1
'Si el ID es igual al de la hoja2 pero la cadena no es igual iniciamos un tercer loop
If .Cells(i, 1) = Sheets("Hoja2").Cells(j, 1) And scadena <> scadena_2 Then
'Recorremos toda la fila hasta encontrar la diferencia y la marcamos en rojo
For n = 1 To col_2
If .Cells(i, n) <> Sheets("Hoja2").Cells(j, n) Then .Cells(i, n).Interior.Color = vbRed
Next n
End If
'vaciamos valor de variable scadena_2
scadena_2 = vbNullString
Next j
'Si el ID no existe en hoja2 recorremos cadena y marcamos diferencias
If p = 0 Then
For x = 1 To col_2
If .Cells(i, x) <> Sheets("Hoja2").Cells(j, x) Then .Cells(i, x).Interior.Color = vbRed
Next x
End If
p = 0
scadena = vbNullString
Next i
End With
End Sub

Esta macro compara los datos de la hoja 1 con la hoja 2 y marca las diferencias en la hoja 1. Por supuesto al ser las bases de datos iguales no sería necesario otra macro que realizase la labor inversa, pero no siempre es así, puede que los registros estén ordenados de forma diferente en una u otra hoja, o que falten registros, etc.

La macro inversa sería esta:

Sub Compara_Hoja2()
'Declaramos variables
Dim scadena As String, scadena_2 As String
Dim i As Long, j As Long, n As Long
Dim col As Long, fin As Long, final As Long, d As Long
Dim a As Long, x As Long, p As Long, col_1 As Long, col_2 As Long
'Trabajamos con la Hoja2
With Sheets("Hoja2")
fin = Application.CountA(.Range("A:A"))
final = Application.CountA(Sheets("Hoja1").Range("A:A"))
col_1 = .Cells(1, Cells.Columns.Count).End(xlToLeft).Column
col_2 = Sheets("Hoja1").Cells(1, Cells.Columns.Count).End(xlToLeft).Column

'Iniciamos primer loop recorriendo registros hoja2
For i = 2 To fin
'Componemos cadena con toda la fila hoja2
For d = 1 To col_1
scadena = scadena & .Cells(i, d).Value
Next d
'Iniciamos segundo loop buscando valor de scadena de hoja1 en hoja1
For j = 2 To final
'Componemos cadena de toda la fija hoja1
For a = 1 To col_2
scadena_2 = scadena_2 & Sheets("Hoja1").Cells(j, a).Value
Next a
'Si el ID existe en la hoja2 contamos
If .Cells(i, 1) = Sheets("Hoja1").Cells(j, 1) Then p = p + 1
'Si el ID es igual al de la hoja1 pero la cadena no es igual iniciamos un tercer loop
If .Cells(i, 1) = Sheets("Hoja1").Cells(j, 1) And scadena <> scadena_2 Then
'Recorremos toda la fila hasta encontrar la diferencia y la marcamos en rojo
For n = 1 To col_2
If .Cells(i, n) <> Sheets("Hoja1").Cells(j, n) Then .Cells(i, n).Interior.Color = vbRed
Next n
End If
'vaciamos valor de variable scadena_2
scadena_2 = vbNullString
Next j
'Si el ID no existe en hoja2 recorremos cadena y marcamos diferencias
If p = 0 Then
For x = 1 To col_2
If .Cells(i, x) <> Sheets("Hoja1").Cells(j, x) Then .Cells(i, x).Interior.Color = vbRed
Next x
End If
p = 0
scadena = vbNullString
Next i
End With
End Sub

Y el resultado de aplicar esas macros es el siguiente:

MACRO PARA HALLAR DIFERENCIAS ENTRE DOS BASES DE DATOS3

Como podéis ver, el código funciona perfectamente y detecta las diferencias en cada registro. La clave para hacer que sea un poco más rápido y los loop no ralenticen el proceso es generar cadenas de texto por cada fila, de este modo lo que comparamos son las filas y no las celdas.

En este caso, utilizamos de guía el ID, pero serviría cualquier identificador que sea común en ambas tablas.

Os dejo otra macro para borrar los colores:

Sub borra_hoja1()
Application.ScreenUpdating = False
With Sheets("Hoja1")
.Range("A2").Select
Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select
With Selection.Interior
.Pattern = xlNone
End With
.Range("A2").Select
End With
End Sub

Descarga el archivo de ejemplo pulsando en: MACRO PARA HALLAR DIFERENCIAS ENTRE DOS BASES DE DATOS

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Hola a todos!

Hace unos días recibí una consulta en la que me solicitaban el poder capturar el valor de una celda en función de si la celda de la fila inferior tenía datos.

Por ejemplo, imaginad que tenemos dos filas, en la primera unas fechas y en la segunda unos importes, pues bien necesitamos extraer las fechas de la primera y última celda con datos en la fila de los importes:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

Para resolver esta necesidad, (que con una macro sería muy sencillo), con una fórmula lo podemos hacer con una función matricial.

Para extraer la primera fecha, el 3 de enero:

{=DESREF(INDICE(B2:O2;COINCIDIR(FALSO;(B2:O2="");0));-1;0)}

Y para extraer la última fecha, el 11 de enero:

{=DESREF(INDICE((B2:O2);;MAX(SI((B2:O2)<>"";COLUMNA(B2:O2)))-1);-1;0)}

En ambos casos, primero detectamos tanto el valor inicial o el final, sin tener en cuenta las celdas vacías, esto nos dará el número de columna, luego simplemente usamos la función desref para indicar que queremos la fila anterior (-1).

El resultado es el siguiente:

DETECTAR VALOR DE LA PRIMERA Y ULTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR1

Como podéis comprobar, las fórmulas funcionan perfectamente.

Y eso ha sido todo, espero que os resulte de utilidad:

Descarga el archivo de ejemplo pulsando en: DETECTAR VALOR DE LA PRIMERA Y ÚLTIMA CELDA CON DATOS Y MOSTRAR LA CELDA SUPERIOR

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Hola a todos!

En el post de hoy voy a tratar de ayudar y dar respuesta a un lector que me comentaba la necesidad de usar SmartArt para generar un diagrama de Gantt personalizado.

(*Antes de entrar en materia, si estáis usando Excel 2010 debéis leer el post completo dado que tendréis que realizar una pequeña modificación en el código).

En un principio, y teniendo en cuenta la naturaleza y objetivo de un diagrama de Gantt, creo que la mejor solución es utilizar una estructura de jerarquía horizontal, para ello me voy a basar en este objeto de SmartArt para realizar el ejemplo (Jerarquía multinivel en horizontal)

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Teniendo en cuanta esto, antes de comenzar a programar, debemos diseñar nuestro cuadro de mando para introducir todos los ítems que necesitamos para confeccionar nuestro diagrama, para este ejemplo utilizaré estas tareas:

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT1

Aunque se podría programar para obtener automáticamente el porcentaje de consecución de objetivos de acuerdo con la fecha, para este ejemplo he decidido que solo se calculan de forma automática los días entre fechas, el resto es a criterio del usuario el llevar el seguimiento y controlar el %.

Como podéis observar, estoy utilizando diferentes niveles de tareas, dado que algunas conforman otras de entidad superior, por ejemplo las diferentes fases de diseño o de desarrollo. Esto implicará que en nuestra estructura de jerarquía se muestren esas dependencias y ofrezca mayor información visual.

En realidad este diseño puede personalizarse de muchas formas, tantas como le gusten al usuario.

Pues bien, una vez que tenemos esta información en la hoja “DATOS”, ya podemos utilizar el siguiente código para generar nuestro peculiar diagrama de Gantt:

Sub DIAGRAMA_GANTT_SMARTART()
'Declaramos variables
Dim Diseño As SmartArtLayout
Dim Shape As Excel.Shape
Dim oNodos As SmartArtNodes
Dim inserta As Shape
Dim i As Integer, Fin As Integer
Dim j As Integer, valor As Double
Dim NPorcent As String, Per_2 As Long, Per_1 As Long
With Sheets("ESTRUCTURA")
.Select
'Eliminamos TODOS objetos en la hoja "ESTRUCTURA"
For Each Shape In .Shapes
Shape.Delete
Next
'Insertamos objeto SmartArt, en este caso "Jerarquía Multinivel"
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/HorizontalMultiLevelHierarchy")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set oNodos = inserta.SmartArt.AllNodes
'Verificamos número de nodos necesarios contando los ítems de la página "DATOS"
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Creamos nodos
Do While oNodos.Count < Fin
oNodos.Add.Promote
Loop
'Eliminamos nodos sobrantes y los nombramos con la información de la hoja "DATOS"
For i = 2 To Fin
Do While oNodos(i - 1).Level < Sheets("DATOS").Range("B" & i).Value
oNodos(i - 1).Demote
Loop
Next
'Eliminamos último nodo (estará vacío al tener encabezado la hoja "DATOS")
oNodos(Fin).Delete
'aplicamos estilos
For Each Shape In .Shapes
'Colores
Shape.SmartArt.Color = Application.SmartArtColors("urn:microsoft.com/office/officeart/2005/8/colors/accent2_1")
'Estilos rápidos
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles("urn:microsoft.com/office/officeart/2005/8/quickstyle/simple2")
'Iniciamos loop para recorrer el diagrama desde el último item al primero
For j = Fin To 2 Step -1
'Si el % está vacío, asignamos un valor 0 a la variable valor
If Sheets("DATOS").Range("F" & j).Value = Empty Then
valor = 0
ElseIf Sheets("DATOS").Range("F" & j).Value > 1 Then
valor = 1
Else
valor = Sheets("DATOS").Range("F" & j).Value
End If
'expresamos en color el porcentaje de cumplimiento de objetivos
With oNodos(j - 1).Shapes.Fill
.TwoColorGradient Style:=msoGradientVertical, Variant:=1
.GradientStops(2).Color = vbWhite
.GradientStops(1).Position = valor
.GradientStops(2).Position = valor
.GradientStops(1).Color.RGB = RGB(204, 204, 255)
End With
'Adicionalmente añadimos el porcentaje en número al diagrama y lo coloreamos en azul
With oNodos(j - 1)
.TextFrame2.TextRange.Text = Sheets("DATOS").Range("A" & j) & " " & Format(valor, "Percent")
NPorcent = Sheets("DATOS").Range("A" & j) & " " & Format(valor, "Percent")
Per_1 = UBound(Split(NPorcent)) + 1
Per_2 = UBound(Split(NPorcent)) + 2
.TextFrame2.TextRange.Words(Per_1).Font.Fill.ForeColor.RGB = vbBlue
.TextFrame2.TextRange.Words(Per_2).Font.Fill.ForeColor.RGB = vbBlue
End With
Next j
'Dimensionamos la imagen
With .Shapes(1)
.Height = 581.25 'Alto del objeto
.Width = 600.5 'Ancho del objeto
.Top = 100 ' Altura en la hoja
.Left = 14.25 ' A la izquierda de la hoja
End With
Next
End With
End Sub

Una vez aplicado el código generamos el siguiente diagrama:

PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT2

Efectivamente, logramos nuestro propósito, el % de consecución se equipara al color azul, que se va desplazando a la derecha a medida que vamos cumpliendo los plazos. Este efecto lo conseguimos con esta parte de la macro:

With oNodos(j - 1).Shapes.Fill
.TwoColorGradient Style:=msoGradientVertical, Variant:=1
.GradientStops(2).Color = vbWhite
.GradientStops(1).Position = valor
.GradientStops(2).Position = valor
.GradientStops(1).Color.RGB = RGB(204, 204, 255)
End With

Al superponer los dos gradientes conseguimos evitar el degradado de los dos colores y que aparezca una línea delimitadora entre el azul y el blanco.

Importante, si estáis programando con Excel 2010 debéis añadir un factor de corrección en la siguiente línea de código, y es añadir un -0.01 al final:

.GradientStops(1).Position = valor - 0.01

Esto se corrige en las versiones 2013 y 2016 (comprobado), pero en 2010 es necesario rebajar en un 0.01 puntos el gradiente nº 1 para que no se muestren los colores difuminados. El ejemplo lo dejo sin el 0.01 dado que estoy programando en Excel 2016 y no es necesario.

También es muy interesante la forma en la que podemos colorear parte de un texto, dado que lo que hacemos es detectar las dos últimas palabras (el número y el símbolo del porcentaje) y aplicarle un color del sistema (azul):

.TextFrame2.TextRange.Words(Per_1).Font.Fill.ForeColor.RGB = vbBlue
.TextFrame2.TextRange.Words(Per_2).Font.Fill.ForeColor.RGB = vbBlue

Aunque podríamos seguir introduciendo elementos en cada nodo, creo que con este ejemplo se consigue, con una simple visual, hacer una idea del nivel de desarrollo en el que se encuentra nuestro proyecto.

En cuanto a la información de la hoja “DATOS”, usamos toda la información excepto las fechas y los días. Los datos obligatorios para generar el diagrama son las tareas, la jerarquía y los porcentajes (que van desde el 0% al 100%).

Y esto esto, espero que os resulte de interés : ) Os dejo dos archivos, uno para 2010 y el otro para versiones superiores.

Descarga el archivo de ejemplo pulsando en: PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT

Descarga el archivo de ejemplo pulsando en: PROGRAMAR SMARTART PARA GENERAR UN DIAGRAMA DE GANTT_v2010

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

CONTAR PALABRAS EN EXCEL

Hola a todos!

Ayer un lector me pedía un fórmula para poder contar palabras en Excel. Supongo que era para delimitar en un informe la cantidad de palabras que un usuario podía escribir.

Lo cierto es que es muy común delimitar de alguna forma el espacio de escritura en los informes, aunque suele hacerse por caracteres escritos y no tanto por palabras, sin embargo también es posible hacerlo.

Para conseguirlo, y sin necesidad de acudir a la programación, tan solo necesitaremos usar la combinación de varias fórmulas:

=SI(LARGO(A2)=0;0;LARGO(ESPACIOS(A2))-LARGO(SUSTITUIR((A2);" ";""))+1)& " palabras"

Con esta función, donde combinamos las funciones LARGO y SUSTITUIR será muy sencillo. Contamos todos los caracteres en nuestra frase (incluidos los espacios en blanco) y le restamos los mismos caracteres pero sin esos espacios en blanco, la diferencia es el número de palabras. Usaremos un condicional para en caso de no existir palabras nos ponga un “0”.

El resultado de la fórmula es este:

CONTAR PALABRAS EN EXCEL

Descarga el archivo de ejemplo pulsando en: CONTAR PALABRAS EN EXCEL

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

PASAR INFORMACIÓN DE UN RANGO A UNA CELDA USANDO FÓRMULAS

Hola a todos!.

El post de hoy va a tratar sobre cómo podemos pasar el contenido de un rango a una celda usando solo fórmulas y sin recurrir a VBA.

En un post anterior os comentaba la forma de hacerlo con macros: PASAR INFORMACIÓN DE UN RANGO A UNA CELDA

Y también recordar que con las últimas actualizaciones de Excel (pero con una suscripción a Office 365) esto se puede realizar fácilmente con la función UnirCadenas.

Pero este post está pensado para esas situaciones de emergencia, cuando no tenemos tiempo ni para programar ni tampoco acceso a office 365 (por los motivos que sean).

Veamos un ejemplo, en la columna A tenemos un texto que está distribuido en diferentes celdas:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS

La idea es pasar la información a una única celda y además dejarla colocada con la misma estructura en vertical añadiendo saltos de línea.

Necesitaremos una celda auxiliar para realizar nuestro trabajo, en la que vamos a usar la función concatenar y seleccionar el rango con los datos:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS1

A continuación, seleccionamos la fórmula y pulsamos F9 en el teclado. El resultado es que se mostrarán los datos que se han añadido a la matriz de la función:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS2

Ahora lo que tenemos que hacer es eliminar el igual = y los paréntesis { } y nos quedará una cadena de texto con la información lista para tratar con una última fórmula:

PASAR INFORMACION DE UN RANGO A UNA CELDA USANDO FORMULAS3

Efectivamente, con la última fórmula utilizamos una función anidada con “sustituir”:

=SUSTITUIR(SUSTITUIR(B9;CARACTER(34);" ");";";CARACTER(10))

En la fórmula inicial, sustituimos las comillas dobles CARACTER(34), por un espacio y en la segunda (la anidada), sustituimos el punto y coma por un salto de línea CARACTER(10).

El resultado es el que podéis ver, hemos pasado la información de un rango a una celda e incluso hemos dejado el mismo formato.

Aunque no sea un proceso limpio con una sola función o usando macros, estoy seguro que en algún momento os ayudará en alguna situación apurada (a mí me ha ayudado en más de una ocasión).

No dejaré archivo de prueba porque se trata de un proceso semiautomático.

Y eso es todo!, hasta el próximo post 🙂

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS

Hola a todos, ¿qué tal estáis?, ¡espero que bien!.

Hace unos días envié a un lector las instrucciones para ejecutar un procedimiento programado en Access usando el programador de tareas de windows. En el post de hoy mostraré la manera de poder hacer de una forma muy sencilla en Excel.

Antes de comenzar vamos a seleccionar una tarea que queramos que se ejecute todos los días y cuya ejecución se realice mediante una macro. Por ejemplo que todos los días a las siete de la mañana nuestro ordenador cierre Microsoft Word en nuestro equipo.

Para ello, tenemos en esta web una macro que sirve para cerrar programas, entre ellos, word: CERRAR UN PROGRAMA DESDE EXCEL CON VBA

Y vamos a añadir en el módulo de Thisworkbook:

Private Sub Workbook_Open()
Call Cierra_Programa
End Sub

A continuación os mostraré cómo acceder al programador de tareas, lo más fácil es usar el buscador de nuestra barra de herramientas o preguntar a “Cortana” por el programador de tareas:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS

Una vez que lo tenemos abierto, se nos mostrarán una serie de pantallas que iremos completando:

En la primera pantalla se nos presentan varias acciones a realizar, en nuestro ejemplo, elegiremos crear una tarea básica:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS2

A continuación, debemos nombrar nuestra tarea y añadir un breve descripción. Luego pulsamos en “Siguiente”:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS3

En la siguiente pantalla “Desencadenar”, vamos a indicar la periodicidad con la que queremos que se ejecute nuestra tarea y la hora:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS4

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS5

En el siguiente paso debemos marcar la opción “Iniciar un programa“:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS6

Y en la siguiente pantalla debemos seleccionar el archivo en el que hemos incluido la macro para cerrar word:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS7

Finalmente pulsamos en finalizar y se nos mostrará un resumen con los pasos realizados:

EJECUTAR UNA MACRO CON EL PROGRAMADOR DE TAREAS DE WINDOWS8

Ahora ya tenemos la tarea creada y, cuando lleguen las siete de la mañana, windows abrirá el archivo que hemos indicado y, como hemos incluido un macro en el evento workbook.open:

Private Sub Workbook_Open()
Call Cierra_Programa
End Sub

La macro se ejecutará en el instante Excel se abra. Os recomiendo que modifiquéis la seguridad de macros para evitar que las macros queden bloqueadas por el propio Excel.

Si en algún momento queréis prescindir de una tarea, solo tenéis que volver al programador, localizar la tarea y eliminarla.

Para este ejemplo, no voy dejar archivo, dado que es puramente teórico, os propongo que la práctica la realicéis vosotros según vuestras necesidades.

Importante: Para que funcione debéis dejar el ordenador encendido, no es necesario que Excel esté abierto, ese trabajo lo hará Windows por nosotros.

Y eso es todo, espero que os haya sido de utilidad.

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

TRABAJAR CON FECHAS PARA RECONSTRUIR HISTÓRICOS

Hola a todos!.

Después de varios días sin publicar en la web, por fin tengo un poco de tiempo para escribir un breve post.

Muchas de las consultas que me envían las suelo resolver muy rápido cuando no son demasiado complejas o cuando son para resolver una parte específica de una rutina o una función. Hoy quiero publicar una de esas consultas, que aunque es sencilla y puede resultar obvia, hay que tener en cuenta que muchos lectores están empezando con Excel tanto en fórmulas como en macros, y siempre está bien detenerse en este tipo de ejercicios.

En este caso, un lector me enviaba una serie de fechas de un histórico pero que solo las fechas de inicio y me preguntaba la forma de obtener automáticamente la fecha final del periodo, esta es la información de partida:

TRABAJAR CON FECHAS PARA RECONSTRUIR HISTORICOS

Pues bien, este trabajo lo podemos hacer de una manera sencilla con una fórmula:

=SI(A2=A3;FECHA(AÑO(B3);MES(B3);DIA(B3)-1);"")

O incluso más sencillo todavía:

=SI(A2=A3;B3-1;"")

Y obtenemos el siguiente resultado:

TRABAJAR CON FECHAS PARA RECONSTRUIR HISTORICOS1

Como podéis ver, obtenemos la fecha fin y cuando llegamos a la última fecha del ID lo dejamos en blanco dado que la fecha debe quedar abierta.

La razón de utilizar la función fecha en la fórmula es para mostrar cómo podemos traducir esta función en lenguaje VBA. Es decir, en VBA no podemos usar directamente la función DATE dado que no permite introducir los argumentos de año, mes y día. Para poder trasladar la función fecha a VBA debemos usar DateSerial, aquí os dejo la macro:

Sub FECHAS()
'Declaramos variables
Dim i As Integer, Fin As Integer
Dim fecha As Date
'Trabajamos en la hoja2
With Sheets("Hoja2")
Fin = Application.CountA(.Range("A:A"))
'calculamos la fecha de cierre del registro actual
For i = 2 To Fin
fecha = .Cells(i + 1, 2)
If .Cells(i, 1) = .Cells(i + 1, 1) Then
.Cells(i, 3) = DateSerial(Year(fecha), Month(fecha), Day(fecha) - 1)
Else
.Cells(i, 3) = ""
End If
Next i
End With

End Sub

De esta forma podemos introducir las funciones Year, Month y Day con la variable “fecha”. Una vez ejecutada la macro, este es el resultado:

TRABAJAR CON FECHAS PARA RECONSTRUIR HISTORICOS2

Aunque a nivel VBA es una rutina sencilla, seguro que en algún momento os puede resultar útil, ya sea el uso de DateSerial o de la propia función fecha().

Aunque no lo comenté al principio, en este ejemplo es obvio que antes de aplicar la fórmula o ejecutar la macro, si los datos están desordenados, debemos ordenar primero por fecha y luego por ID.

Descarga el archivo de ejemplo pulsando en: TRABAJAR CON FECHAS PARA RECONSTRUIR HISTÓRICOS

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

Donate Button with Credit Cards

¡¡Muchas gracias!!