View original (French)

The famous day and month inversion VBA

Nicoroth Posted messages 55 Status Member -  
tompols Posted messages 1273 Registration date   Status Contributor Last intervention   -
Hi,

I've done a bit of digging online to see why when I create a macro to format dates, VBA switches the day and the month.
I've found that quite a few people are struggling with this... and so am I.
So, a little help would be welcome, let me explain,

here is my function that allows me (manually) to reformat dates that are in column K (because I use these dates in another sheet and need them reformatted to appear in the form dd/mm/yyyy)
Doing this manually works perfectly, but I want to create a little macro that allows me to do this, and this macro below switches the day and the month.
After my research, I saw that the CDate function could help prevent the program from making this switch, however, as a beginner in VBA, I don't know (after several attempts) how to write the function...

Maybe I'm going about this the wrong way, but here it is:

Sheets("Sheet1").Select
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(10, 1)), TrailingMinusNumbers:=True
Sheets("Sheet2").Select

If anyone has an idea, it would help me a lot.

Thank you
Configuration: Windows 2003 / Internet Explorer 7.0

7 answers

  1.
    tompols Posted messages 1273 Registration date   Status Contributor Last intervention   460
     
    Hi
    try using TEXT(K3;"jj/mm/yyyy") instead of K3 :)
    --
    By a beautiful solution, we mean the simple and easy solution to a difficult and complicated problem.
    1
  2. tompols Posted messages 1273 Registration date   Status Contributor Last intervention   460
     
    Hi,
    could you please post an example file with your values in column K (www.cjoint.com)?
    --
    A beautiful solution is understood to be the simple and easy solution to a difficult and complicated problem.
    0
  3. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Hello,
    try with.. 
     Columns("K").NumberFormat = "dd/mm/yyyy"

    See you!
    --
    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;)-(Confucius)
    0
  4. Nicoroth Posted messages 55 Status Member
     
    I’m looking at what I can do.
    0
  6. Nicoroth Posted messages 55 Status Member
     
    In fact, I looked a bit at my file and I think the problem isn't really where I thought.

    What’s happening is that I have these dates in column K and a lot of other information between columns C and S.

    My goal is to use a concatenate function in column B. This works well except for the dates, because when I run the concatenate, the date in the format dd/mm/yyyy becomes a 5-digit number which must be a specific reference for Excel.

    For example, the date 15/05/2010 in this form in column K becomes 40313 in my concatenate.

    Why? Personally, I don't understand. Is there a function we can add in the concatenate at the K3 level?

    Here's my formula:

    =CONCATENATE(H3;" ";I3;". ";;"Obs: ";L3;" id: ";C3;" Art: ";E3;" Cli: (";M3;"). ";S3;" L. ";;R3;" Unids. FI: ";K3;". FD: ";O3)

    Thank you
    0
  7. picsou
     
    Pourquoi ? Personnellement, je ne comprends rien. Y a-t-il une fonction que l'on peut ajouter dans le CONCATENATE au niveau de K3 ?
    0
  8. lermite222 Posted messages 9042 Status Contributor 1 199
     
    But yes, the date is converted into a serial date, a number starting from the year 01/01/1900.
    You can use this number as a date by formatting the column, or with DateValue if it's in VBA.
    And that's the best part, since you can also display it however you want with "Format".
    See you later
    --
    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)
    0