VBA Excel: Date Format Solved

Question

Hello,

I can't format a date entered in a form:
When closing the form, the entered data is copied into cells of a spreadsheet; I have set the cells in the first column of my spreadsheet to a "Date" format in the form "dd/mm/yyyy".
In the module (see code below), I force the value of the textbox to this format:

 If saisie.date_ev.Value <> "" Then If Not IsDate(saisie.date_ev.Value) Then MsgBox "Incorrect date.", vbCritical + vbOKOnly, "Error" saisie.date_ev.Value = "" saisie.date_ev.SetFocus saisie.Hide saisie.Show Else saisie.date_ev.Value = Format(saisie.date_ev.Value, "dd/mm/yyyy") End If End If

However, despite all these precautions, the date copied into the cell is in the format "mm/dd/yyyy".
I don't want to change the regional preferences.

Does anyone have a solution?

Thank you in advance.

Armojax · Accepted Answer

Hello Misfit,

The date formats assigned to cells in Excel do not all translate the same way in VBA.

Thus, a cell formatted as dd/mm/yy in Excel is translated in VBA as: NumberFormat = dd/mm/yy. Normal.
Whereas a format defined as dd/mm/yyyy is translated as: NumberFormat = m/d/yyyy, which changes everything.

In practice, in VBA, reverse the months and the days.

Armojax.

Kobaya · Answer

Hi Misfit,

when you write:
saisie.date_ev.Value = Format(saisie.date_ev.Value, "dd/mm/yyyy"), you are putting a string in your cell.

try this:
saisie.date_ev.Value = Cdate(saisie.date_ev.Value)

See you,
Kobaya.

misfit · Answer

Hi Kobaya,

Thanks for your reply but it doesn't work. Just like with Format, my date appears in the form dd/mm/yyyy in my textbox but when I copy the content of this textbox into the cell, the date changes to the format mm/dd/yyyy.

Does anyone have another idea ... I'm cracking up

Cheers
Misfit

random · Answer

and if you looked at your cell's format if you enter a date in a cell it will take the format of the cell

random · Answer

and then convert your value to cdate(textbox)

Nours · Answer

I have exactly the same problem when opening a .txt file via VBA in Excel. If you find a solution, let me know; otherwise, if I find one on my side, I will definitely let you know.

Yann MICHEL · Answer

Suggestion:
When recording the value of the text box to your cell, proceed as follows:
cells(x, y).value = format(Userform.Textbox, "dd/MM/yyyy")

To define the format dd/MM/yyyy, the month must be in English and uppercase. It's the same for the PartDate function.

Hoping this works for you.
Michou

Alban · Answer

From someone I was able to help through this thread: thank you!

nini · Answer

Hello,
I can't extract the semesters of the year using the PartDate function in Access. I’m trying to use it in a query but I don’t know the parameter to extract the semester based on a list of entered dates. Thank you for helping me. Nini

crogiez · Answer

d="08/22/2008 12:30:42" activecell...value=datevalue(t) possibly change cell format  -- 15 scenarios for RCT3 on my site http://crogiez.free.fr

freto · Answer

Well, I'm reviving this topic, don't want to make another one.
I have a date issue with an Excel spreadsheet: on my PC, and on my manager's PC, no problem, I can do in VBA:
activeSheet.Cells(parcoursLigne, "H").NumberFormatLocal = "dd/mm/yyyy"
However, on another person's PC, outside the department, whose configuration I don't know, it's impossible to do it: the format dd/mm/yyyy does not exist.
If you could shed some light on the solution....thanks in advance

--
What's tiny, and yellow and very very dangerous?????
A canary with the super-user password.
Mouhahahaha

Duderino_23 · Answer

Kubaya rules !!!!  I had the same problem with the date format and it's fixed thanks to "Cdate" !!!  Thank you so much !

freto · Answer

Well it's been a while, but I found it with a Cdate and I don't remember which manipulation (I think we had to set the thing in English but still in French, anyway).
Now it works but I can't say more about the solution
--
What's tiny, and yellow and very very dangerous?????
A canary with the super-user password.
Mouhahahaha

turbo92 · Answer

Hello,

I have a similar problem, but in a different scenario.
I want to copy the content of one cell to another, and
in the first one it displays "01/03/2010" correctly, but in
the second one, it reverses the day and the month to "03/01/2010"
when the macro copies and pastes the data from one cell to another.

I want to point out that both sheets (the copy cell and the paste cell) are
different but belong to the same workbook, and the cells are
configured for French dates.

Thank you for your help.

sarrus · Answer

récupère ta date en format long

Nicoroth · Answer

Hi,  I had a similar problem, I pasted my dates into a column that I wasn't using with the formula:  =TEXT(K2,"dd/mm/yyyy") with the date in question in K2....  For me, it did the same thing as you when I had to concatenate, but thanks to this little trick, it works.  Hoping this helps you.

SSAMAN · Answer

2008-05-03

Sefran · Answer

You need to test the value and type of cell
MsgBox ("The Value property is: " & Cell.Value)
MsgBox ("The Text property is: " & Cell.Text)

We see that "Value" is the value of the date and "Text" is the display format of the date.

If the cell contains a text that is not interpreted as a date in its display form, it needs to be converted as follows

For Each Cell In Selection
If IsDate(Cell.Value) Then
Cell.Value = DateValue(Cell.Value)
End If
Next Cell

Ironmano · Answer

Hello forum!
Just a little bump a few years later, since I have a "similar" problem.
Let me explain:
- I'm importing a .txt file into xls. In this file, various data + 2 columns (B and F) with dates in the format dd/mm/yyyy
- with a VBA macro, I copy the data from this file
- I close the file
- I paste the data from this file into another workbook

Until last Thursday (31/05/2012), everything was fine... but since June 1st, when transferring from one file to another, my 01/06/2012 becomes 06/01/2012...

I have read your various suggestions, but how to set the format of these imported data in 2 columns without losing this format during the copy-paste?

Thank you in advance.

EDM

6boulettes · Answer

Dim datduJour As String Application.StatusBar = Format(datduJour, "dd/mm/yyyy")  ==> allows for the date to be in the format DD/MM/YYYY regardless of the medium used (textbox or cell).

moncoteyang · Answer

You can try formatting your cell with a formatting type:  Selection (your cell) = CDate(CSng(Date))  Found this solution on a forum because I faced the same issue....  See you later!

VBA Excel: Date Format

21 réponses

Texture issues in enshrouded

Announcement: online source code archive

Pc that doesn't run normally

Keyboard issue with the "alt gr" key

End of the movie room 1408

Pc startup black screen white dash blinking

Edit mp3 file properties

Freebox telephony no dial tone

Pc won't start - pxe issue (e61 and m0f)

I would like to change the date on my digitec watch.