Date format in a listbox
 

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

Date format in a listbox
 

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

Date format in a listbox
 

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

Date format in a listbox
 

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

Date format in a listbox
 

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

Date format in a listbox
 

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.ListC ount - 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