VBA reverses day with month in variables of type Date

Asked

Viewed 440 times

-1

Context: I have the following code that receives from the user a date:

dataAtualDefault = Day(Date) & "/" & Month(Date) & "/" & Year(Date)
dataInsercao = Application.InputBox("Data da entrada", "Entrada", dataAtualDefault)

Problem: if the user commits a typo and enters a date when the month is greater than 12, such as 09/15/2020, the value of the month is reversed with that of the day, and the variable becomes the following value: 15/09/2020

Solutions I have tried but failed:

  1. This did not work because the month and day are reversed before the If
    If(Month(dataInsercao ) > 12) Then
        MsgBox("Erro!")
    End If
  1. Format doesn’t help because it converts to a String, but I really need the variable to be of the Date type
Format(dataInsercao , "dd/mm/yyyy")
excel vba date excel-vba visual-basic-6

  • It is not that, it is that the date is in the American that is mês/dia/ano, what you will do with the date?

  • Yes, it is in US format. But how to remove it? The date provided by the user will be inserted in a column with several other dates. Then, the date entered by the user is compared with the others to know if the organization of all dates should be made

  • 1

    if you are entering where? ta confused your question

  • The date is inserted in an excel column

2 answers

0

I tested the code and no error appears... if you insert it into an excel cell try to format that cell for date first. With Worksheets("sheet1").Range("B1") .NumberFormat = "dd/mm/yyyy" .Value = Date End With

-1

I found a solution to the problem. Enter the data like this in excel: Sheets("Example"). Cells(1, 1). Value = Dateadd("yyyy", 0, VBA.Format(Userform1.Textbox1.Value, "dd/mm/yyyy"))

Works 100%

Browser other questions tagged excel vba date excel-vba visual-basic-6

You are not signed in. Login or sign up in order to post.