month, year and date are coming wrong

[drom]

Hi and Thanks in Advance![/]

I have a column with dates and Times llike eg:


12/07/2011 12:19
13/07/2011 7:40
30/08/2011 8:44

the formatting is dd/mm/yyyy h:mm;@

and I have to take the Year, Month and Day from them 'CAN'T use formulas on the Sheet
'Remember this code is just for this web

At present I am using the following code:

Sub MACRO1()
  On Error Resume Next:     Application.ScreenUpdating = False
Dim rPT As Range:           Set rPT = ActiveSheet.Range("A2:A500")
  Dim aPT():                aPT = rPT.Value
Dim xRow As Long

Dim vBreakDate As Variant
Dim vBrakDateTime As Variant


'rPT.Cells(xRow, 1) = "12/07/2011 12:19"
'rPT.Cells(xRow, 2) = "13/07/2011 7:40"
'rPT.Cells(xRow, 3) = "30/08/2011 8:44"



  For xRow = LBound(aPT, 1) To UBound(aPT, 1)
    vBrakDateTime = Split(rPT.Cells(xRow, 1), " ")
    ' Date = vBrakDateTime(0)
    ' Time = vBrakDateTime(1)
    vBreakDate = Split(vBrakDateTime(0), "/")
          ActiveSheet.Cells(xRow , 4) = vBreakDate(2) 'YEAR   ?????
          ActiveSheet.Cells(xRow , 5) = vBreakDate(1) 'MONTH  ?????   in my computer Works fine
          ActiveSheet.Cells(xRow , 6) = vBreakDate(0) 'DAY    ?????
  Next xRow

End Sub

In My Computer I get on the ActiveSheet:
yyyy mm dd
2011 7 12
2011 7 13
2011 8 30

so FINE.

But I my users computers they get:
yyyy mm dd
2011 12 7
2011 13 7
2011 30 8

so they get the Day instead of the month



And I don't know why
We are actually using the same workbook prepared for my to check what's wrong

DO I have to check country's code, language and/or???

What am I missing?


Thanks!

 

[Jose_Chasez]

Assuming they are on a Windows machine, check their short date settings (in the Windows Control Panel).

 

[Roli001]

Try using the date functions vs trying to break a date into a string:

Sub MACRO1()
  On Error Resume Next:     Application.ScreenUpdating = False
Dim rPT As Range:           Set rPT = ActiveSheet.Range("A2:A500")
  Dim aPT():                aPT = rPT.Value
Dim xRow As Long

Dim vBreakDate As Variant
Dim vBrakDateTime As Variant


'rPT.Cells(xRow, 1) = "12/07/2011 12:19"
'rPT.Cells(xRow, 2) = "13/07/2011 7:40"
'rPT.Cells(xRow, 3) = "30/08/2011 8:44"



  For xRow = LBound(aPT, 1) To UBound(aPT, 1)
    vBrakDateTime = Split(rPT.Cells(xRow, 1), " ")
    ' Date = vBrakDateTime(0)
    ' Time = vBrakDateTime(1)
    vBreakDate = Split(vBrakDateTime(0), "/")
         <strike> 'ActiveSheet.Cells(xRow, 4) = vBreakDate(2)  'YEAR   ?????</strike>
         <strike> 'ActiveSheet.Cells(xRow, 5) = vBreakDate(1)  'MONTH  ?????   in my computer Works fine</strike>
          <strike>'ActiveSheet.Cells(xRow, 6) = vBreakDate(0)  'DAY    ?????</strike>
          
          Cells(xRow, 4) = Year(rPT.Cells(xRow, 1))
          Cells(xRow, 5) = Month(rPT.Cells(xRow, 1))
          Cells(xRow, 6) = Day(rPT.Cells(xRow, 1))
  Next xRow

End Sub

 

[drom]

Try using the date functions vs trying to break a date into a string:

Cells(xRow, 4) = Year(rPT.Cells(xRow, 1))
Cells(xRow, 5) = Month(rPT.Cells(xRow, 1))
Cells(xRow, 6) = Day(rPT.Cells(xRow, 1))
Next xRow

End Sub
[/code]

Unfortunately when doing what you say, I did already try to do so.

the year goes up eg:

31/08/2011 19:33:14

it's giving me:
YEAR= 2013
Month=7
DAY=8
And even worst.

This is happenig just in some users computers, Because in my computer always goes FINE.

 

[Norie]

Sounds like a good 'ole US/ROW date format problem.

That date is in dd/mm/yy, but for some reason VBA is interpreting it as being mm/dd/yy.

Try putting DateValue around rPT.Cells(xRow, 1) when you use the date functions that will give you a 'real' date.

Or put the 'real' date in a variable.

Dim dtPT As Date
 ...
 
dtPT = DateValue(rPT.Cells(xRow, 1))
 
Cells(xRow, 4) = Year(dtPT)
Cells(xRow, 5) = Month(dtPT)
Cells(xRow, 6) = Day(dtPT)

 

[knightofni]

Non code based solution, but why not ask your users to format the cells to display a yyyy mm dd format?