Problem with date format when transcribing it to a box with VBA - Excel

Question

Problem with date format when transcribing it to a box with VBA - Excel

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)
#4 by (0 votes)
#5 by (0 votes)

2

I am working on a macro in which through a form and a text box called fechasolicitudreq . I'm saving the dates in the corresponding cells.

I have tried the following formats:

Cells(i,j).Value = Me.fechasolicitudreq.Value
Cells(i,j).Value = Format(Me.fechasolicitudreq.Value, "Short Date")
Cells(i,j).Value = Format(Me.fechasolicitudreq.Value, "dd/mm/yyyy")

But the following happens to me:

If date is 10/01/2017 in the box you type 1/10/2017
If date is 01/13/2017 in the box you type 01/13/2017

What's more, the big dilemma is that the results of the functions Month() and Day() are also affected. For tests performed, the dd is assigned as day if this is greater than 12 , but otherwise, it is assigned as month (and that it is appreciated in the functions).

I wish I could give it the date format in Spanish, but in case it does not, at least the functions Day() and Month() are coherent, to be able to use that information.

Note: I have also tried to concatenate an empty string, but automatically change it to date .

vba excel

asked by Emaneitron 13.01.2017 в 16:09

source

5 answers

Request to external XML files How to implement IPN notifications, Mercado Pago, for partial payments?

score 1 · Answer 1

1

Did you try formatting the same cell?
In Cell Format / Number / Custom
and verify that the box has the format dd / mm / yyyy maybe it has the format d / mm / yyyy .

answered by 13.01.2017 в 16:25

score 1 · Answer 2

1

Try this, I hope it works for you:

Passes the date in number format: cells (i, j) = CDbl (CDate (datesrequestreq))

Change the format on the sheet: cells (i, j) .NumberFormat="dd / mm / yyyy"

answered by 31.01.2017 в 20:31

score 0 · Answer 3

What is happening is that you are converting a date data into text and when you pass it to Excel, it interprets it by following its own algorithms for it, in which the regional configuration of the user's equipment intervenes. It would be best if you passed a date object and used the number format options in the cell to display the data in the way you want it.

Additional information:

According to Data types Date ( my emphasis)

Date variables show dates according to the short date format recognized by the team . The hours are displayed according to the time format (12 hours or 24 hours) recognized by the team .

to understand what is happening, I would have to do it is to know the regional configuration of your team, however, as the configuration of each team can vary and in the same team can vary from one moment to another, the use of the short format is very inconvenient.

score 0 · Answer 4

The same thing happened to me. You must pass the data format of the cells to "Text" to correctly interpret the dates. Do it like this (for 31 rows and in the second column, for example):

For i = 1 To 31
    With Worksheets(NombreHoja)
        .Cells(i, 2).NumberFormat = "@"
    End With
Next

score 0 · Answer 5

I was with the same problem. At first I had a variabla 'date' that I defined as Public in a module. and it started working well. but then I defined on the same line another variable published 'Date_est'. and there I stop working 'date' I loaded the date on the sheet with the format mm / dd / yy.

solve it defined in separate lines in module 1 the 2 variables ' Public Date As Date Public fecha_est As Date ' greetings