Date Format

[Guest]

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?

 

[RB Smissaert]

What is Tb1?

RBS

 

[RB Smissaert]

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

RBS

 

[Guest]

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

 

[Guest]

Hi

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

hazel

What is Tb1?

RBS

 

[Guest]

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.

 

[RB Smissaert]

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

RBS

Hi

Sorry I forgot that !! Tb1 is a TextBox1 on a Userform

 

[Guest]

TB1 is a string, not a date.

Plus, I would check on what is so clear.

 

[Guest]

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.