FUNCIÓN DIASEM, FORMAT, TEXT, DATEDIFF Y DATEDIF PARA CALCULO DE FECHAS EN VBA

Hola a todos ¿Qué tal os va? supongo que muchos de vosotros acabando las vacaciones o al igual que en mi caso, esperando a que lleguen 🙂

Mientras tanto, he estado trasteando un poco con las posibilidades que ofrece VBA en cuando a fechas y su tratamiento. He probado algunas funciones que pueden resultar útiles ejecutándolas en Visual. Esta entrada puede ser algo así como una colección de códigos para convertir fechas en diferentes formatos y valores, estoy seguro que algunos de ellos os van a resultar útil en vuestros trabajos.

Como ejemplo para este post, imaginad que contáis con una serie de fechas de nacimiento, y queréis extraer diferentes informaciones para luego analizar cuál os puede resultar más útil. Lo primero que vamos a utilizar es la función DIASEM, para poder extraer en número de día (siendo el Lunes el 1 y el Domingo el 7) de una fecha concreta de nacimiento, la función a utilizar en VBA es “Weekday”, en el código lo veremos mejor:

.Cells(i, 2).Value = Application.Weekday(Worksheets(1).Cells(i, 1).Value, 2)

El resultado, por ejemplo para mi fecha de nacimiento 11/05/1979, según la fórmula nací el quinto día de la semana, es decir un viernes. Alguien que naciese el 25/10/1960 nació un martes. Aquí podéis ver la información:

VARIAS_FECHAS_VBA_1

A partir de esta función tendréis codificados los días en formato numérico y además conforme con la norma ISO 8601, sección 3.17.

Pero resulta que además queremos el literal del día, es decir, que ponga “viernes”, “martes” y que se siga refiriendo al día de la semana de la fecha de referencia. Pues bien, podemos utilizar la siguiente función:

.Cells(i, 3).Value = Format(Worksheets(1).Cells(i, 1).Value, "dddd")

Utilizanto “format” definimos el formato de fecha que queremos, en este caso el literal de los días “dddd” especificado en el segundo argumento. El resultado es el siguiente:

VARIAS_FECHAS_VBA_2

Siguiendo con la misma fórmula, ahora queremos en las dos siguientes columnas, el número de mes y el literal de mes, por ejemplo, en mi caso el mes “5” y literal “mayo”. Para ello vamos a usar las siguientes funciones:

'Número mes
.Cells(i, 4).Value = Format(Worksheets(1).Cells(i, 1).Value, "mm")
'Literal mes
.Cells(i, 5).Value = Format(Worksheets(1).Cells(i, 1).Value, "mmmm")

Y el resultado es este:

VARIAS_FECHAS_VBA_3

Una vez que hemos “desglosado” el detalle de la fecha, necesitamos expresarla de forma completa, es decir, para la fecha 11/05/1979 pues: viernes, 11 de mayo de 1979 La función para realizar esta tarea es la siguiente:

.Cells(i, 6).Value = Format(Worksheets(1).Cells(i, 1).Value, "dddd, dd mmmm yyyy")

Y ahora ya tenemos todos los datos dispuestos  🙂

VARIAS_FECHAS_VBA_4

Como curiosidad, estas informaciones las podemos extraer también utilizando otra función “text” que es la misma que en la hoja utilizamos con la fórmula texto y con la que podemos también formatear fechas. Sin embargo, una vez trasladada al editor e invocándola con “application” nos ofrece los datos en inglés, estas son las funciones para literal día, literal mes y fecha completa en inglés:

'Día en inglés
.Cells(i, 7).Value = Application.Text(Worksheets(1).Cells(i, 1).Value, "dddd")
'Mes en inglés
.Cells(i, 8).Value = Application.Text(Worksheets(1).Cells(i, 1).Value, "mmmm")
'Fecha completa en inglés
.Cells(i, 9).Value = Application.Text(Worksheets(1).Cells(i, 1).Value, "dddd, dd mmmm yyyy")

El resultado lo muestro en el área sombreada gris (la que está en inglés):

VARIAS_FECHAS_VBA_5

Todo lo visto hasta ahora se refiere a datos que se extraen sobre una única fecha, pero podríamos tener necesidad de obtener datos en relación a dos fechas, o lo que es lo mismo, en relación al día actual. Es decir, podemos preguntarnos, ¿cuántos años hace que he nacido?, ¿cuántos meses? ¿cuántos días?. Esto también lo podemos hacer y existe una función específica en vba para ello, y es “Dateiff”, similar al “sifecha” pero con diferencias, como veremos más adelante.

Estos datos están expresados en valores absolutos, es decir, el total de meses, días y años. Y las fórmulas se introducen de la siguiente forma:

AÑOS = DateDiff("YYYY", CDate(Worksheets(1).Cells(i, 1).Value), Date)
MESES = DateDiff("M", CDate(Worksheets(1).Cells(i, 1).Value), Date)
DIAS = DateDiff("D", CDate(Worksheets(1).Cells(i, 1).Value), Date)
'Luego lo pasamos a la hoja:
.Cells(i, 10).Value = AÑOS
.Cells(i, 11).Value = MESES
.Cells(i, 12).Value = DIAS

Es resultado es:

VARIAS_FECHAS_VBA_6

Así pues, vemos que para mi ejemplo y teniendo en cuenta el día de hoy,  mi vida se resume en: 36 años o 435 meses o 13258 días. Es un dato que puede ser interesante, pero lo que realmente es útil, es conocer esas mismas cifras pero relacionadas entre ellas, es decir x años y x meses y x días. Que es lo que precisamente podemos hacer con la función sifecha() en la hoja excel. Pero el Dateiff no puede proporcionar estos cálculos, tan solo hace referencia a unidades “absolutas”.

Para poder lograr los datos requeridos debemos introducir la fórmula en VBA, es decir, introducir Dateif (ahora el if con una sola “f”). y lo vamos a hacer así:

AÑO = "=DATEDIF(RC[-12],TODAY(),""Y"")"
MES = "=DATEDIF(RC[-13],TODAY(),""YM"")"
DIA = "=DATEDIF(RC[-14],TODAY(),""MD"")"
'AÑOS El número de años naturales completos del período.
.Cells(i, 13).Value = AÑO
.Cells(i, 13).Value = CDbl(.Cells(i, 13).Value)
'MESES_ El número de meses desde fecha_inicial hasta el mes de fecha_final.
'Los días y los años de las fechas se omiten.
.Cells(i, 14).Value = MES
.Cells(i, 14).Value = CDbl(.Cells(i, 14).Value)
'DIAS_El número de días desde fecha_inicial hasta fecha_final.
'Los meses y los años de las fechas se omiten
.Cells(i, 15).Value = DIA
.Cells(i, 15).Value = CDbl(.Cells(i, 15).Value)

Así podremos lograr la información requerida si problema, introducimos la fórmula y luego la llevamos a la hoja y así nos dará la información en relación a los años y en relación a los meses. Como sabéis si introducís fórmulas en el código, luego en la hoja también aparecerán esas fórmulas, y si estamos ejecutando macros, esto ralentiza el proceso de forma significativa, la solución: igualar a valores, ejemplo: .Cells(i, 15).Value = CDbl(.Cells(i, 15).Value)

El resultado es el siguiente:

VARIAS_FECHAS_VBA_7

Ahora puedo tener la información que necesitaba, hasta la fecha, desde mi nacimiento han pasado: 36 años, 3 meses y 17 días. 🙂  Casi nada.

Debo indicar, que entre el Dateiff y el Dateif se producen algunas diferencias y son las siguientes, la primera fórmula al no contar con las referencias a los meses y los días, redondea los datos, por ejemplo, en fechas con mes mayor o igual a agosto, redondea al año, en meses a partir del día 15 redondea al mes. Sin embargo, todo entra en la lógica y el tipo de datos que necesitemos.

Por último, si queremos pasar los datos de este último cálculo a una literal completo, es decir (mi caso) 36 años y 3 meses y 17 días, solo tenemos que concatenar los datos de las tres columnas y ya está:

.Cells(i, 16).Value = .Cells(i, 13).Value & " " & "años" & " " & .Cells(i, 14).Value & " " _
& "meses" & " y " & .Cells(i, 15).Value & " " & "dias"

y así queda nuestra última columna:

VARIAS_FECHAS_VBA_8

Para finalizar os dejo la macro completa con el resto de programación y anotaciones adicionales:

Sub FechasVBA()
Dim i As Double
Dim j As Double
Application.ScreenUpdating = False
Final = Application.CountA(Worksheets(1).Range("a:a"))
If Worksheets(1).Range("B1") <> Empty Then
Sheets(1).Range("B1:P" & Final).ClearContents
End If
For i = 2 To Final
With Worksheets(1)
'DIA DE LA SEMANA LUNES 1 DOMINGO 7 de acuerdo con la norma ISO 8601, sección 3.17
'Numero
.Cells(1, 2).Value = "DIA NUMERO"
.Cells(i, 2).Value = Application.Weekday(Worksheets(1).Cells(i, 1).Value, 2)
'Español Letra
.Cells(1, 3).Value = "DIA LITERAL"
.Cells(i, 3).Value = Format(Worksheets(1).Cells(i, 1).Value, "dddd")
'Español número
.Cells(1, 4).Value = "MES NUMERO"
.Cells(i, 4).Value = Format(Worksheets(1).Cells(i, 1).Value, "mm")
'Español Letra
.Cells(1, 5).Value = "MES LITERAL"
.Cells(i, 5).Value = Format(Worksheets(1).Cells(i, 1).Value, "mmmm")
'Español fecha completa
.Cells(1, 6).Value = "FECHA COMPLETA"
.Cells(i, 6).Value = Format(Worksheets(1).Cells(i, 1).Value, "dddd, dd mmmm yyyy")
'Ingles Letra
.Cells(1, 7).Value = "DIA LITERAL INGLES"
.Cells(i, 7).Value = Application.Text(Worksheets(1).Cells(i, 1).Value, "dddd")
'Ingles Letra
.Cells(1, 8).Value = "MES LITERAL INGLES"
.Cells(i, 8).Value = Application.Text(Worksheets(1).Cells(i, 1).Value, "mmmm")
'Ingles fecha completa
.Cells(1, 9).Value = "FECHA COMPLETA INGLES"
.Cells(i, 9).Value = Application.Text(Worksheets(1).Cells(i, 1).Value, "dddd, dd mmmm yyyy")
AÑOS = DateDiff("YYYY", CDate(Worksheets(1).Cells(i, 1).Value), Date)
MESES = DateDiff("M", CDate(Worksheets(1).Cells(i, 1).Value), Date)
DIAS = DateDiff("D", CDate(Worksheets(1).Cells(i, 1).Value), Date)
'AÑOS_El número de años naturales completos del período.
'A partir de Agosto suma un año más.
.Cells(1, 10).Value = "AÑOS TOTALES"
.Cells(i, 10).Value = AÑOS
'MESES_El número de meses indicados completos del período
'A partir del día quince suma un mes
.Cells(1, 11).Value = "MESES TOTALES"
.Cells(i, 11).Value = MESES
'DIAS_El número de días indicados completos del período
.Cells(1, 12).Value = "DIAS TOTALES"
.Cells(i, 12).Value = DIAS
AÑO = "=DATEDIF(RC[-12],TODAY(),""Y"")"
MES = "=DATEDIF(RC[-13],TODAY(),""YM"")"
DIA = "=DATEDIF(RC[-14],TODAY(),""MD"")"
'AÑOS El número de años naturales completos del período.
.Cells(1, 13).Value = "AÑOS RELATIVOS"
.Cells(i, 13).Value = AÑO
.Cells(i, 13).Value = CDbl(.Cells(i, 13).Value)
'MESES_ El número de meses desde fecha_inicial hasta el mes de fecha_final.
'Los días y los años de las fechas se omiten.
.Cells(1, 14).Value = "MESES RELATIVOS"
.Cells(i, 14).Value = MES
.Cells(i, 14).Value = CDbl(.Cells(i, 14).Value)
'DIAS_El número de días desde fecha_inicial hasta fecha_final.
'Los meses y los años de las fechas se omiten
.Cells(1, 15).Value = "DIAS RELATIVOS"
.Cells(i, 15).Value = DIA
.Cells(i, 15).Value = CDbl(.Cells(i, 15).Value)
'TIEMPO TRANSCURRIDO
.Cells(1, 16).Value = "TIEMPO TRANSCURRIDO"
.Cells(i, 16).Value = .Cells(i, 13).Value & " " & "años" & " " & .Cells(i, 14).Value & " " _
& "meses" & " y " & .Cells(i, 15).Value & " " & "dias"
End With
Next
'Marcamos en negrita la cabecera de las columnas con datos
Fin = Application.CountA(Worksheets("Hoja1").Range("1:1"))
For j = 1 To Fin
With Worksheets(1)
Range(.Cells(1, 1), .Cells(1, j)).Select
With Selection
Selection.Font.Bold = True
Range("A1").Select
End With
End With
Next
'Para terminar:
With Cells
'Ajustamos ancho de columnas.
.EntireColumn.AutoFit
'Alineamos al centro los datos.
.HorizontalAlignment = xlCenter
End With
Application.ScreenUpdating = True
End Sub

Y con esto ya tenemos toda la información que necesitábamos. Ha sido un ejercicio interesante, tanto por las funciones utilizadas como la técnica para introducirlas y ejecutarlas en VBA.

Las últimas partes del código son para relatar los títulos, ajustar el ancho y centrar el texto de las columnas, me ha parecido buena idea para completar la macro 🙂

Descarga el archivo de ejemplo pulsando en: FUNCION DIASEM FORMAT TEXT DATEIFF Y DATEIF CON VBA

 

Anuncios

4 pensamientos en “FUNCIÓN DIASEM, FORMAT, TEXT, DATEDIFF Y DATEDIF PARA CALCULO DE FECHAS EN VBA

  1. Buen día!!! Soy asiduo lector de tu blog y me encanta la sencillez con que explicas!!! Poco a poco voy nutriendo mi conocimiento en EXCEL y VBA y lo aplico a mis labores diarias. Si quisiese formar una cadena de texto “Compras correspondientes al mes de (aquí va el mes según la fecha que está en otra celda) de (y aquí el año)” cómo será la fórmula a aplicar? Atento a tu respuesta y saludos desde Venezuela!!!

    Le gusta a 1 persona

    • Hola Pedro:
      Me alegro que los post te sean de utilidad. Muchas gracias!.

      La cadena que puedes utilizar es esta (suponiendo una fecha en la celda A1): ="Compras correspondientes al mes"&" "& MES(A1)&" de "& AÑO(A1)
      Saludos.

      Si quieres que el mes aparezca en letra, tendrías que utilizar esta otra variante:

      ="Compras correspondientes al mes de"&" "& ELEGIR(MES(A1);"enero";"febrero";"marzo";"abril";"mayo";"junio";"julio";"agosto";"septiembre";"octubre";"noviembre";"diciembre")&" de "& AÑO(A1)

      Saludos

      Le gusta a 1 persona

      • Hola Segu!!! Como soy muy necio, después de escribirte seguí cacharreando y logré esta:
        =”COMPRAS CORRESPONDIENTES EL MES DE “&MAYUSC(TEXTO(A1;” mmmm”))& ” DE “&TEXTO(A1;”yyyy”),
        ya que la frase está escrita en mayúsculas.La que Ud. me indica es más sencilla. Creo que le dí muchas vueltas, es lo bonito de Excel, que un problema puede tener muchas soluciones, ahí está el aprender cada día más. Agradecido por su atención y espero que esta variante también sirva como solución a otros lectores que tienen a bien seguir sus enseñanzas. Saludos y un gran abrazo!!!

        Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s