Date Format
 

Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

What is Tb1?

RBS


"Hazel" wrote in message
...
Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

And what is the format of ws.Cells(freerownum, 7) ?

RBS

"Hazel" wrote in message
...
Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

In VBA if a string can be interpreted as a US formatted date, it will be.
Try this

With ws.Cells(freerownum, 7)
.Value = DateValue(Tb1)
.NumberFormat = "dd/mm/yy"
End With

--
Regards,
Tom Ogilvy




"Hazel" wrote:

Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

Hi

Sorry I forgot that !! Tb1 is a TextBox1 on a Userform
--
Many thanks

hazel


"RB Smissaert" wrote:

What is Tb1?

RBS


"Hazel" wrote in message
...
Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

Perhaps its the variables. The following:

Sub hazel()
Set ws = Sheets("sheet1")
freerownum = 1
Tb1 = Now
ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")
End Sub

puts:
9/8/2007
in the cell - clearly in day/month/year format.
--
Gary''s Student - gsnu200736


"Hazel" wrote:

Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

I think Tom O's suggestion will do the job.

RBS


"Hazel" wrote in message
...
Hi

Sorry I forgot that !! Tb1 is a TextBox1 on a Userform
--
Many thanks

hazel


"RB Smissaert" wrote:

What is Tb1?

RBS


"Hazel" wrote in message
...
Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

TB1 is a string, not a date.

Plus, I would check on what is so clear.

--
Regards,
Tom Ogilvy




"Gary''s Student" wrote:

Perhaps its the variables. The following:

Sub hazel()
Set ws = Sheets("sheet1")
freerownum = 1
Tb1 = Now
ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")
End Sub

puts:
9/8/2007
in the cell - clearly in day/month/year format.
--
Gary''s Student - gsnu200736


"Hazel" wrote:

Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel

Date Format
 

Hi

Everybody's help much appreciated, I have used and tried Tom's solution and
it works perfectly even on the other 3 textboxes that are used to input dates
on the sheet - hopefully because I save all the snippets of code etc that
help me I might never again need anything about UK / US date formatting.

--
Many thanks

hazel


"Tom Ogilvy" wrote:

In VBA if a string can be interpreted as a US formatted date, it will be.
Try this

With ws.Cells(freerownum, 7)
.Value = DateValue(Tb1)
.NumberFormat = "dd/mm/yy"
End With

--
Regards,
Tom Ogilvy




"Hazel" wrote:

Hi

What is wrong? with the line below

ws.Cells(freerownum, 7).Value = Format(Tb1, "dd/mm/yy")

On the sheet it converts it to US Format ("mm/dd/yy")

Why?
--
Many thanks

hazel