VBA Excel: Date Format

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.

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

and then convert your value to cdate(textbox)

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

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

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

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

récupère ta date en format long

2008-05-03

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

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).

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.

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

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

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

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!

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

Kubaya rules !!!!

I had the same problem with the date format and it's fixed thanks to "Cdate" !!!

Thank you so much !

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.