Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
My personal gremlin seems to be that,for no apparent reason (although I am
sure there must be one) when moving dates around in VBA they change from UK format to US format. A date I pick up from, say, a text box as 12/01/2010 (being 12th Jan) will arrive in the prescribed cell as 01/12/2010. I seem to have tried everything including assigning the date to a variable using the datevar = format([Date from text box], "dd/mm/yyyy") in an attempt to force it to behave. Nothing seems to work consistently. I am using Excel 07 and my machine locale setting is UK. I am sure that I must be missing a trick but I'm not sure what. Any ideas, anyone? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
That is a VBA 'feature', it only knows US dates.
Cast it CDate(TextBox1.Text) HTH Bob "Vagabond" wrote in message ... My personal gremlin seems to be that,for no apparent reason (although I am sure there must be one) when moving dates around in VBA they change from UK format to US format. A date I pick up from, say, a text box as 12/01/2010 (being 12th Jan) will arrive in the prescribed cell as 01/12/2010. I seem to have tried everything including assigning the date to a variable using the datevar = format([Date from text box], "dd/mm/yyyy") in an attempt to force it to behave. Nothing seems to work consistently. I am using Excel 07 and my machine locale setting is UK. I am sure that I must be missing a trick but I'm not sure what. Any ideas, anyone? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
Cells that are formated as General should take on the internation settings. I believe in 2007 there may be some bugs and are revverting to US format. Make sure you have all the office updates. Using the floowing code will not solve this problem datevar = format([Date from text box], ) If the cell is general formating it will perform the same algorithm as if you manually typed the date and do a conversion. Yo could also have the cells format as "dd/mm/yyyy" which could cause the problem. The solution would be to format the cells as "dd/mm/yyyy" or use in a formula then following datevar.numberformat = "dd/mm/yyyy" -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169184 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
I use Dateserial to get the elements of the date and combine them in the
appropriate format. I've also found that cell formatting will override the date being entered, so if the cell is formatted in US format then that will override how the date is presented from the textbox. "Vagabond" wrote: My personal gremlin seems to be that,for no apparent reason (although I am sure there must be one) when moving dates around in VBA they change from UK format to US format. A date I pick up from, say, a text box as 12/01/2010 (being 12th Jan) will arrive in the prescribed cell as 01/12/2010. I seem to have tried everything including assigning the date to a variable using the datevar = format([Date from text box], "dd/mm/yyyy") in an attempt to force it to behave. Nothing seems to work consistently. I am using Excel 07 and my machine locale setting is UK. I am sure that I must be missing a trick but I'm not sure what. Any ideas, anyone? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
Dates are stored in excel as a number with 1 = Jan 1, 1900. Every day increments by 1 so Jan 2, 1900 is 2. An hour is stored as 1/24 and a minute is stored a 1/(24*60) with midnight equa 0. So noon is equal to 1/2 (.5) and 6:00AM equals 1/4 (.25). Formating a date doesn't change the whay it is stored into the spreadsheet. A date is stored as the number equivant. Tformating only change the way the date iis displayed. My guess is this is not an excel problem. since you are copying form text boxes the date goes into the clipboard. So there is a problem either going from the text box to the clip board or from the clip board into excel. You could try opening up Notepad and see what happens if you copy a date from excel into Notepad and from Notepad to excel. This may help isolate the problem. You can also view the clip board data by opening up the TaskPane from the worksheet view menu and then pressing the down arrow in the View Pane and select ClipBoard. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169184 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
Hi,
An example : http://cjoint.com/?botWd33EPR "Vagabond" a écrit dans le message de groupe de discussion : ... My personal gremlin seems to be that,for no apparent reason (although I am sure there must be one) when moving dates around in VBA they change from UK format to US format. A date I pick up from, say, a text box as 12/01/2010 (being 12th Jan) will arrive in the prescribed cell as 01/12/2010. I seem to have tried everything including assigning the date to a variable using the datevar = format([Date from text box], "dd/mm/yyyy") in an attempt to force it to behave. Nothing seems to work consistently. I am using Excel 07 and my machine locale setting is UK. I am sure that I must be missing a trick but I'm not sure what. Any ideas, anyone? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with date formats
What clipboard?
I guess you are US based so you don't see the problem, but I can assure you that the problem is in the way VBA treats dates. Unless a date is unambiguous, such as 13/8/2009, VBA will treat it as a US date, regardless of your date settings. So, if I, who is UK based, enter 12/10/2009 in a textbox, and then drop that textbox value into a cell, it ends up as 10th Dec 2009, ignoring my format for clarity. As I said, the solution is to cast the textbox value. such as Range("A1").Value = CDate(TextBox1.Text) Bob "joel" wrote in message ... Dates are stored in excel as a number with 1 = Jan 1, 1900. Every day increments by 1 so Jan 2, 1900 is 2. An hour is stored as 1/24 and a minute is stored a 1/(24*60) with midnight equa 0. So noon is equal to 1/2 (.5) and 6:00AM equals 1/4 (.25). Formating a date doesn't change the whay it is stored into the spreadsheet. A date is stored as the number equivant. Tformating only change the way the date iis displayed. My guess is this is not an excel problem. since you are copying form text boxes the date goes into the clipboard. So there is a problem either going from the text box to the clip board or from the clip board into excel. You could try opening up Notepad and see what happens if you copy a date from excel into Notepad and from Notepad to excel. This may help isolate the problem. You can also view the clip board data by opening up the TaskPane from the worksheet view menu and then pressing the down arrow in the View Pane and select ClipBoard. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=169184 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different date formats | Excel Discussion (Misc queries) | |||
Different Date formats in text to be recognised as date value | Excel Programming | |||
Cell Date and Listbox Date formats | Excel Programming | |||
Dealing with date formats on expiration date | Excel Programming | |||
Problems with number formats when using Find/Replace | Excel Discussion (Misc queries) |