i am having a bit of a problem with date format
manipulation. I capture a date from a text box on a form
and write it to a cell on a worksheet, in the process the
date switches from UK (dd/mm/yy) to US (mm/dd/yy) format,
despite the fact that it appears on the form in the UK
style and the cell in the worksheet has the UK date format
set. Any advice?

This is the code fragment if it is on any use:

A) tmpStr = Form1.Textbox1.text

B) Selection.Formula = tmpstr

Fragment (A) is in one procedure fragment (B) is in
another, no other manipulation takes place. I have even
tried using Format(tmpstr, "dd/mm/yy")

Murray

I think xl will see you putting tmpstr in the cell and then it uses the windows
regional settings to see what order the mdy should be.

If you're settings are USA (mdy), then it'll try to make it a date using that.
Finally, it'll figure out what to show in the cell by the cell's format.

So if I plop 10/12/2003 into a cell (with my windows regional settings in mdy
order), I'll get Oct 12, 2003.

But if I do the same thing with dmy, I'll get Dec 10, 2003.

I'm not sure of a good way to force an unambiguous date using just one
textbox--maybe 3 (month, day & year???) or a calendar control???

But if you know you're expecting dd/mm/yy, maybe you could parse the string into
its pieces and then:

with no validity checks:

Option Explicit
Sub testme()

Dim TmpStr As String
Dim TmpArr As Variant

TmpStr = InputBox("dmy style")
TmpArr = Split97(TmpStr, "/")

Selection.Value = DateSerial(TmpArr(UBound(TmpArr)) + 2000, _
TmpArr(LBound(TmpArr) + 1), _
TmpArr(LBound(TmpArr)))
Selection.NumberFormat = "mmm dd, yyyy" 'just for verification
End Sub

'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function


If you're using xl2k or higher, you can replace split97 with split and delete
the function. (Split was added in xl2k.)



Murray Taylor wrote:

i am having a bit of a problem with date format
manipulation. I capture a date from a text box on a form
and write it to a cell on a worksheet, in the process the
date switches from UK (dd/mm/yy) to US (mm/dd/yy) format,
despite the fact that it appears on the form in the UK
style and the cell in the worksheet has the UK date format
set. Any advice?

This is the code fragment if it is on any use:

A) tmpStr = Form1.Textbox1.text

B) Selection.Formula = tmpstr

Fragment (A) is in one procedure fragment (B) is in
another, no other manipulation takes place. I have even
tried using Format(tmpstr, "dd/mm/yy")

Murray

--

Dave Peterson

Dave,

Many thanks for your detailed reply. Iam using Xl2000 on XP Pro, I have
everything set to use the local UK (dd/mm/yyyy) settings and have played
about with this for ages. Working from your examples I eventually
sorted the problem by actually picking the tmpstr date from the text
box, formatting it to the US format then processing it via my code. It
now reports the date in the UK format when viewed in the sheet.

Very weird!

Murray
(remove mapson from email address)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Murray

You may have more success with

A) tmpStr = Form1.Textbox1.tex

B) Selection.Value = CDate(tmpstr

James.

Thanks for the suggestion,I'll give it a go.

Murray
(remove mapson from email address)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!