APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

Hola a todos ūüôā

Espero que os vaya muy bien!. En el post de hoy vamos a trabajar con ADO para dar formato de fecha a una cadena de texto.

El tema de las fechas aunque puede parecer sencillo, siempre es susceptible de complicarse. Y en muchas ocasiones el problema se debe al formato con el que llegan los datos que nos envían. En el post de hoy vamos a trabajar uno de esos formatos.

Vamos a ilustrarlo con un ejemplo, imaginad que abrís una petición en la red a favor del control de horarios y ruidos de los locales de ocio nocturno (¡todo un reto!). Y la aplicación que ha recogido los datos os envía un archivo con la siguiente información:

DAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

Efectivamente, los campos NOMBRE, PETICI√ďN y FIRMA no tienen nada de particular, pero el campo FECHA DE FIRMA deber√≠a contar con un formato de fecha y si embargo nos llega como un texto. Esto es un problema, dado que a priori no vamos a poder trabajar f√°cilmente con este tipo de fechas y es m√°s, necesitamos exportar algunas de las columnas del fichero a otra aplicaci√≥n para generar otro tipo de informaciones.

Hemos elegido ADO para transferir la informaci√≥n de la hoja BASE a la hoja INFORMACI√ďN y ser√° en ese proceso en el que vamos a utilizar SQL para formatear las fechas y tambi√©n controlar aquellas celdas que se encuentren vac√≠as.

Para hacer el trabajo, os dejo esta macro que es capaz formatear solo las celdas que contienen la fecha:

Sub CONEXION_SQL_FECHAS()
'Declaramos variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim Fin As Integer, i As Long, MiLibro As String, Tit As String
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Eliminamos datos de hoja INFORMACION anteriores
Sheets("INFORMACION").Select
With Sheets("INFORMACION")
'Eliminamos datos de la consulTa SQL anterior
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then .Range("A2:D" & Fin).ClearContents
'Realizamos consulta SQL, y componemos un string para crear los datos y darle formato de fecha
obSQL = "SELECT [BASE$].[NOMBRE], [BASE$].[PETICION], [BASE$].[FIRMA], " & _
"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA] " & _
"FROM [BASE$] "
MiLibro = ActiveWorkbook.Name
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Pegamos datos y a√Īadimos encabezados
Do Until Dataread.EOF
Dataread.MoveFirst
.Cells(2, 1).CopyFromRecordset Dataread
For i = 0 To Dataread.Fields.Count - 1
Tit = Dataread.Fields(i).Name
.Cells(1, i + 1) = Tit
Next
Loop
'Por seguridad formateamos la columna FECHA DE FIRMA a fecha
.Columns("D:D").NumberFormat = "m/d/yyyy"
'liberamos y desconectamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End With
Application.ScreenUpdating = True
End Sub

Como podéis observar,  usamos ADO para importar la información de una hoja a la otra, y en el proceso aprovechamos para realizar los cambios y controles necesarios. En concreto, en esta sentencia SQL:

"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA]

Donde controlamos que solo sean formateadas las celdas que contienen datos usando un IIF que valida si son nulas. Si no lo son, componemos una nueva cadena de texto creando la fecha y aplicamos la función CDate que nos devolverá una expresión tipo fecha, ¡Justo lo que queremos!.

Una vez ejecutada la macro, el resultado es el siguiente:

DAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA1

Este es un ejemplo muy concreto donde los datos a formatear deben ser texto, de hecho, cuando no nos envían la información con formato de fecha, casi siempre viene así.

Antes de finalizar, os recuerdo la necesidad de marcar en las referencias la librería de ADO Activex Data Objects 2.8 Library, es importante que lo hagáis, de lo contrario, la macro no va a funcionar:

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado2

Y eso es todo, espero que os resulte de utilidad a la hora de trabajar con fechas en Excel ūüôā

Descarga el archivo de ejemplo pulsando en: APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

 
¬Ņ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

CALCULAR D√ćAS, MESES Y A√ĎOS ENTRE DOS FECHAS Y DIFERENTES PERIODOS

Hola a todos!!

Hace unos d√≠as recib√≠ una consulta sobre el c√°lculo de d√≠as, meses y a√Īos entre dos fechas, pero aplicado a varios periodos, es decir, el obtener los d√≠as, los meses y los a√Īos entre dos fechas es sencillo cuando aplicamos la funci√≥n SIFECHA(), pod√©is profundizar un poco m√°s en este post:¬†CALCULAR A√ĎOS ENTRE DOS FECHAS EN EXCEL.

Es aplicar la funci√≥n sifecha() para obtener los d√≠as, los meses y los a√Īos entre las dos fechas, y donde los elementos se relacionan entre s√≠, es decir, no son datos absolutos, sino que los meses van acumulando los d√≠as y los a√Īos van acumulando los meses, este ser√≠a un ejemplo:

calcular-dias-meses-y-anos-entre-dos-fechas-y-diferentes-periodos

Las fórmulas usadas para obtener este resultado con:

A√Īos:¬†=SIFECHA(A2;B2; "Y")
Meses: =SIFECHA(A2;B2; "YM")
Días:=SIFECHA(A2;B2; "MD")
Antig√ľedad General:¬†=SIFECHA(A2;B2; "y")& " a√Īos¬† " & SIFECHA(A2;B2; "ym")& " meses¬† " &SIFECHA(A2;B2; "md")& " dias "

Hasta ahora esto es sencillo, pero ¬Ņc√≥mo hacemos para calcular el sumatorio de los a√Īos, los meses y los d√≠as? teniendo muy en cuenta que no podemos sumar simplemente, dado que los d√≠as no pueden ser m√°s que los d√≠as que tiene un mes.

Lo que estamos buscando sería esto (siguiendo nuestro ejemplo):

calcular-dias-meses-y-anos-entre-dos-fechas-y-diferentes-periodos2

Como pod√©is observar, el resultado no es la simple suma de conceptos por columna, sino que existe una relaci√≥n entre d√≠as, meses y a√Īos, de forma que los d√≠as sobrantes se van trasladando a los meses y los meses sobrantes se van trasladando a los a√Īos (en este la suma de los periodos no llegan a un a√Īo).

Para conseguir este resultado vamos a introducir la siguiente fórmula:

Para los días:
=SI(SUMA($E$2:E5)>30,5;SUMA($E$2:E5)-(ENTERO(SUMA($E$2:E5)/30,5)) *(30,5);SUMA($E$2:E5))

Para los meses:
=SI(SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5)>11;((SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5))-(ENTERO((SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5))/12))*12);SUMA($D$2:D5)+((SUMA($E$2:E5)-E6)/30,5))

Para los a√Īos:
=SUMA($C$2:C5) +ENTERO((SUMA($D$2:D5) +((SUMA($E$2:E5)-E6)/12)-D6)/12)

Esta f√≥rmula es bastante conocida en manuales, foros, etc… y me ha parecido interesante dejarla en la web. He realizado alg√ļn ajuste que cre√≠a interesante para reducir el contenido de la f√≥rmula.

Descarga¬†el archivo pulsando en: CALCULAR D√ćAS, MESES Y A√ĎOS ENTRE DOS FECHAS Y DIFERENTES PERIODOS