Hi,
I have read many posts on date formats and many answers from Tom Ogilvy and
others. I learned that if a date in a cell can be interpreted as a US date,
it will. So one can apply a specified format through code like this:
Me.txtDateStart = _
Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
My first problem is now resolved. I have a second one.
I have a two column list of data in the sheet that I load in a list box
through code like this:
Me.lstNumberAndDate.List = _
.Range(strRange).Offset(2, 0).Resize(3, 2).Value
The first column is a number and the second is a date. When I load the data
in the list, the date appears in US format. How can I have it in
international format like the others?
Will I have to split the data in two or load it in two operations? If so,
can someone tell me how?
Thanks.
--
Jac Tremblay

I don't know if this will work but it is worth a try. There are two ways of
putting a date into a cell. One is to store it as text the other as a
number. The Number is refere to as serial date where 1 = Jan 1, 1900 and
increments by one for each day. Hours are stored as 1/24 and minutes as
1/(24 * 60).

The number is the same for every country but the format which they are
displayed may be different for each country. to change the number format of
a cell you use theis

Range("A1").numberformat = "yyyy-mm-dd"

A list box stores all information as text. So when you move data from a
cell to a list box there is a conversion that is performed to convert the
serial date to text.

Tom's solution is to convert the serial date to text using the format
statement

Me.txtDateStart = _
Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")

Your code is using a special apllication in Excel that only works with
"values" of moving a Range of cells into any array (or List) and during the
transfer Excel is doing a conversion from serial number to text.

Me.lstNumberAndDate.List = _
.Range(strRange).Offset(2, 0).Resize(3, 2).Value

From your posting I don't know what format you cells are in the worksheet.
Check by going to the worksheet menu Format - Cells - Number and see which
format is selected. Try changing the format to the format your want like
Date "yyyy-mm-dd". this is equivalent to Range("A1").numberformat =
"yyyy-mm-dd". Rerun your code.

The date format can be in any order with any set of characters seperating th
efields like

Range("A1").numberformat = "dd/mm/yyyy"
Range("A1").numberformat = "dd/mm/yy"
Range("A1").numberformat = "mmm dd, yyyy"
Range("A1").numberformat = "mmmm dd, yyyy"

mmm - is the 3 letter abbreviation of each month
mmmm - is the complete spelling of the month.


"Jac Tremblay" wrote:

> Hi,
> I have read many posts on date formats and many answers from Tom Ogilvy and
> others. I learned that if a date in a cell can be interpreted as a US date,
> it will. So one can apply a specified format through code like this:
> Me.txtDateStart = _
> Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
> My first problem is now resolved. I have a second one.
> I have a two column list of data in the sheet that I load in a list box
> through code like this:
> Me.lstNumberAndDate.List = _
> .Range(strRange).Offset(2, 0).Resize(3, 2).Value
> The first column is a number and the second is a date. When I load the data
> in the list, the date appears in US format. How can I have it in
> international format like the others?
> Will I have to split the data in two or load it in two operations? If so,
> can someone tell me how?
> Thanks.
> --
> Jac Tremblay

Hi Joel,
The cell are formated correctly: the dates are in the international format
"yyyy-mm-dd" as I want them. But during the transfer process, they are
converted to string and they inherit the US format that I do not want.
I read some more on the subject tonight and found a way to add dates in the
correct format in a multicolumn listbox. I cannot use the simple instruction:
Me.lstNumberAndDate.List = _
..Range(strRange).Offset(2, 0).Resize(3, 2).Value
I will have to loop through the cell values and apply the date format to
each value individually.
I will do that tomorrow. It is now bed time.
Thank you for your answer. I appreciate your time and concern.
--
Jac Tremblay


"Joel" wrote:

> I don't know if this will work but it is worth a try. There are two ways of
> putting a date into a cell. One is to store it as text the other as a
> number. The Number is refere to as serial date where 1 = Jan 1, 1900 and
> increments by one for each day. Hours are stored as 1/24 and minutes as
> 1/(24 * 60).
>
> The number is the same for every country but the format which they are
> displayed may be different for each country. to change the number format of
> a cell you use theis
>
> Range("A1").numberformat = "yyyy-mm-dd"
>
> A list box stores all information as text. So when you move data from a
> cell to a list box there is a conversion that is performed to convert the
> serial date to text.
>
> Tom's solution is to convert the serial date to text using the format
> statement
>
> Me.txtDateStart = _
> Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
>
> Your code is using a special apllication in Excel that only works with
> "values" of moving a Range of cells into any array (or List) and during the
> transfer Excel is doing a conversion from serial number to text.
>
> Me.lstNumberAndDate.List = _
> .Range(strRange).Offset(2, 0).Resize(3, 2).Value
>
> From your posting I don't know what format you cells are in the worksheet.
> Check by going to the worksheet menu Format - Cells - Number and see which
> format is selected. Try changing the format to the format your want like
> Date "yyyy-mm-dd". this is equivalent to Range("A1").numberformat =
> "yyyy-mm-dd". Rerun your code.
>
> The date format can be in any order with any set of characters seperating th
> efields like
>
> Range("A1").numberformat = "dd/mm/yyyy"
> Range("A1").numberformat = "dd/mm/yy"
> Range("A1").numberformat = "mmm dd, yyyy"
> Range("A1").numberformat = "mmmm dd, yyyy"
>
> mmm - is the 3 letter abbreviation of each month
> mmmm - is the complete spelling of the month.
>
>
> "Jac Tremblay" wrote:
>
> > Hi,
> > I have read many posts on date formats and many answers from Tom Ogilvy and
> > others. I learned that if a date in a cell can be interpreted as a US date,
> > it will. So one can apply a specified format through code like this:
> > Me.txtDateStart = _
> > Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
> > My first problem is now resolved. I have a second one.
> > I have a two column list of data in the sheet that I load in a list box
> > through code like this:
> > Me.lstNumberAndDate.List = _
> > .Range(strRange).Offset(2, 0).Resize(3, 2).Value
> > The first column is a number and the second is a date. When I load the data
> > in the list, the date appears in US format. How can I have it in
> > international format like the others?
> > Will I have to split the data in two or load it in two operations? If so,
> > can someone tell me how?
> > Thanks.
> > --
> > Jac Tremblay

dim myRng as range
dim myCell as range

with worksheets("somesheetnamehere")
'just the first column
set myrng = .range(strRange).offset(2,0).resize(3,1)
end with

with me.lstnumberanddate
for each mycell in myrng.cells
.additem mycell.value
.List(.ListCount - 1, 1) = format(myCell.Offset(0, 1).Value, "yyyy-mm-dd")
next mycell
end with

(untested, uncompiled. Watch for typos.)

Jac Tremblay wrote:
>
> Hi,
> I have read many posts on date formats and many answers from Tom Ogilvy and
> others. I learned that if a date in a cell can be interpreted as a US date,
> it will. So one can apply a specified format through code like this:
> Me.txtDateStart = _
> Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
> My first problem is now resolved. I have a second one.
> I have a two column list of data in the sheet that I load in a list box
> through code like this:
> Me.lstNumberAndDate.List = _
> .Range(strRange).Offset(2, 0).Resize(3, 2).Value
> The first column is a number and the second is a date. When I load the data
> in the list, the date appears in US format. How can I have it in
> international format like the others?
> Will I have to split the data in two or load it in two operations? If so,
> can someone tell me how?
> Thanks.
> --
> Jac Tremblay

--

Dave Peterson

Hi Dave,
That is exactly the solution I found in the newsgroup. You were faster to
code it than me.
Tomorrow, i will post the exact solution I will use.
Thank you very much. I appreciate.
Good night.
--
Jac Tremblay


"Dave Peterson" wrote:

> dim myRng as range
> dim myCell as range
>
> with worksheets("somesheetnamehere")
> 'just the first column
> set myrng = .range(strRange).offset(2,0).resize(3,1)
> end with
>
> with me.lstnumberanddate
> for each mycell in myrng.cells
> .additem mycell.value
> .List(.ListCount - 1, 1) = format(myCell.Offset(0, 1).Value, "yyyy-mm-dd")
> next mycell
> end with
>
> (untested, uncompiled. Watch for typos.)
>
> Jac Tremblay wrote:
> >
> > Hi,
> > I have read many posts on date formats and many answers from Tom Ogilvy and
> > others. I learned that if a date in a cell can be interpreted as a US date,
> > it will. So one can apply a specified format through code like this:
> > Me.txtDateStart = _
> > Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
> > My first problem is now resolved. I have a second one.
> > I have a two column list of data in the sheet that I load in a list box
> > through code like this:
> > Me.lstNumberAndDate.List = _
> > .Range(strRange).Offset(2, 0).Resize(3, 2).Value
> > The first column is a number and the second is a date. When I load the data
> > in the list, the date appears in US format. How can I have it in
> > international format like the others?
> > Will I have to split the data in two or load it in two operations? If so,
> > can someone tell me how?
> > Thanks.
> > --
> > Jac Tremblay
>
> --
>
> Dave Peterson
>

Hi Dave,
As I said yesterday, here is the code I use:
Dim rngCell As Excel.Range
Dim rng1stCol As Excel.Range
Set rng1stCol = .Range(.Range(strRange).Offset(2, 0), _
.Range(strRange).Offset(2, 0).End(xlDown))
For Each rngCell In rng1stCol
Me.lstNumberAndDate.AddItem rngCell.Value
Me.lstNumberAndDate.List(Me.lstNumberAndDate.ListCount - 1, 1) = _
Format(rngCell.Offset(0, 1).Value, "yyyy-mm-dd")
Next rngCell
It works in Exce 2000 and 2007.
Thanks again for your precious comment.
--
Jac Tremblay


"Dave Peterson" wrote:

> dim myRng as range
> dim myCell as range
>
> with worksheets("somesheetnamehere")
> 'just the first column
> set myrng = .range(strRange).offset(2,0).resize(3,1)
> end with
>
> with me.lstnumberanddate
> for each mycell in myrng.cells
> .additem mycell.value
> .List(.ListCount - 1, 1) = format(myCell.Offset(0, 1).Value, "yyyy-mm-dd")
> next mycell
> end with
>
> (untested, uncompiled. Watch for typos.)
>
> Jac Tremblay wrote:
> >
> > Hi,
> > I have read many posts on date formats and many answers from Tom Ogilvy and
> > others. I learned that if a date in a cell can be interpreted as a US date,
> > it will. So one can apply a specified format through code like this:
> > Me.txtDateStart = _
> > Format(.Range(strRange).Offset(1, 4).Value, "yyyy-mm-dd")
> > My first problem is now resolved. I have a second one.
> > I have a two column list of data in the sheet that I load in a list box
> > through code like this:
> > Me.lstNumberAndDate.List = _
> > .Range(strRange).Offset(2, 0).Resize(3, 2).Value
> > The first column is a number and the second is a date. When I load the data
> > in the list, the date appears in US format. How can I have it in
> > international format like the others?
> > Will I have to split the data in two or load it in two operations? If so,
> > can someone tell me how?
> > Thanks.
> > --
> > Jac Tremblay
>
> --
>
> Dave Peterson
>