Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formats
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formats
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formats
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formats
Murray
You may have more success with A) tmpStr = Form1.Textbox1.tex B) Selection.Value = CDate(tmpstr James. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Formats
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Formats | Excel Discussion (Misc queries) | |||
Date Formats | Excel Discussion (Misc queries) | |||
Date Formats | Excel Discussion (Misc queries) | |||
Date formats | Excel Discussion (Misc queries) | |||
Date Formats | Excel Discussion (Misc queries) |