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:
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:
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:
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 🙂
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):
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:
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:
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:
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
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!!!
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
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!!!
OK, Pedro, es otra alternativa 🙂 Perfecto y gracias por comentar. Saludos!!
Que tal Buenos dias , tengo un problema con las fechas , mi situación concretamente es a contar los días pero que no me cuente los domingos,
Ejemplo Al dar dobleclic en «C4» despliega el calendario y lo que busco es que conforme vaya seleccionando los dias me vaya contando los días elegidos.
ejemplo si selecciono los días a partir del 13 de Agosto al 29 de agosto sin contar los domingos me suman 15 días
o de lo contrario que me pregunte la fecha inicial y cuantos dias quiero agregar a la fecha inicial pero sin contar los domingos
Gracias, adjunto archivo de lo que pretendo, he intendado con fórmula =DIA.LAB.INTL(clfecha,15,11,0) pero me da un día de más, Gracias
Hola Cecilio,
Siento el retraso en la respuesta, pero he estado de vacaciones:
Lo único que tienes que hacer es en la fórmula especificar fecha de inicio y fin y luego elegir solo el domingo como fin de semana (sin necesidad de especificar el festivo) =DIAS.LAB.INTL(G10;H10;11), de esta forma tienes los 15 días que comentas.
Saludos.
amigo saludos, me parecen muy interesantes tus cursos, en cuanto a la funcion datediff llevo mucho tiempo intentando restar dos fechas en un formulario y que el resultado me aparezca en el mismo formulario.gracias, si alguien me puede apoyar
Hola Ramiro:
Creo que esto te puede servir: https://excelsignum.com/2017/07/23/calcular-la-edad-con-datedif-en-un-formulario-de-excel/
Es la función sifecha programada en un formulario.
Saludos