Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dd/mm converted to mm/dd
Hi all, I have a problem with Excel switching dates from dd/mm to mm/dd. I a using Excel 2003. - Dates are stored on the worksheet 'REF' in dd/mm/yy format - Those same dates are the rowsource for a combobox in a userform - When the user clicks 'Submit' on the userform, the date is written t the next blank cell on the worksheet 'DATA'. This cell is also formatte as dd/mm/yy. For some reason, Excel swaps some date formats around when written This only occurs where it is possible to logically do so. i.e. 01/08/06 (dd/mm/yy) becomes 08/01/06 (mm/dd/yy) 17/01/06 (dd/mm/yy) remains 17/01/06 as 01/17/06 is not a valid date. With the example above, the serial value is 38930. When it comes out reversed, the serial value actually changes 38725. So in actual fact not just a change of formatting but a complete change in the date! I have pasted my relevant code below - Private Sub cboDate_Change() With Me.cboDate .Value = Format(.Value, \"dd/mm/yyyy\") End With End Sub Private Sub submitbutton_Click() Dim nextR As String Dim newR As String Sheets(\"Data\").Select Range(\"A1\").Select Lastrow = Cells(Rows.Count, \"A\").End(xlUp).Row nextR = Lastrow + 1 newR = \"A\" & nextR Range(newR).Select ActiveCell.Value = cboDate.Value ActiveCell.Offset(0, 3).Select ActiveCell.Value = cboConsultant.Value ActiveCell.Offset(0, 3).Select ActiveCell.Value = cboStart.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = cboFinish.Value ActiveCell.Offset(0, 2).Select ActiveCell.Value = cboTask1.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = txtTask1.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = cboTask2.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = txtTask2.Value End Sub Could anyone help me out with this one? It has me baffled! System dat formats check out, and Excel standard date is Australian. Thanks Ro -- system ----------------------------------------------------------------------- systemx's Profile: http://www.excelforum.com/member.php...fo&userid=2925 View this thread: http://www.excelforum.com/showthread.php?threadid=56654 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dd/mm converted to mm/dd
Note: I have fixed the issue by leaving the dates in text format all the way through. Still interested in the root cause though if anyone can advise... Cheers :) -- systemx ------------------------------------------------------------------------ systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254 View this thread: http://www.excelforum.com/showthread...hreadid=566546 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dd/mm converted to mm/dd
It would seem it does not matter if you leave in text format as it doe the same thing, I thought I might be able to overcome the problem b have 3 seperate text boxes for dd mm yy and concatenate the cells bu it seems to have a mind of it's own and just resorts to mm dd yy. aaaggghhh??? I to would dearly like a solution to this problem. Regards Darre -- apnda ----------------------------------------------------------------------- apndas's Profile: http://www.excelforum.com/member.php...fo&userid=3613 View this thread: http://www.excelforum.com/showthread.php?threadid=56654 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dd/mm converted to mm/dd
What is this ? :
With Me.cboDate Value = Format(.Value, \"dd/mm/yyyy\") End With Remember that a combo value is just text, it is not ever a date. The above will then make no sense. VB may be helping you with some liberal date conversions, but wrong it is. You can not assign a numberformat to text, and I guess this is where your troubles begin. You should always use Datevalue to ensure that dates are read in the local fashion: ActiveCell.Value = Datevalue("08/01/06") will insert january 8th in dd-mm countries, and august 1st in mm-dd countries. HTH. Best wishes Harald "apndas" skrev i melding ... It would seem it does not matter if you leave in text format as it does the same thing, I thought I might be able to overcome the problem by have 3 seperate text boxes for dd mm yy and concatenate the cells but it seems to have a mind of it's own and just resorts to mm dd yy. aaaggghhh??? I to would dearly like a solution to this problem. Regards Darren -- apndas ------------------------------------------------------------------------ apndas's Profile: http://www.excelforum.com/member.php...o&userid=36137 View this thread: http://www.excelforum.com/showthread...hreadid=566546 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dd/mm converted to mm/dd
I have a just found a solution also, upon reading into it a bit mor that mm/dd/yy is simply the default vba setting irregardless of you computer regional settings. i have just allowed it to put the value i the designated cell and then just reformat the cell to the date forma you require. .Cells(nextRow, 2).Value = txtDay.Value & "/" & txtMonth.Value & "/" txtYear.Value .Cells(nextRow, 2).Value = Format(.Cells(nextRow, 2), "dd/mm/yy") it works so I'm not going to complain an alternative solution "The best approach is to specify a date b using VBA's DateSerial function and let Excel take care of th formatting details (it will use the user's short date format)" - Joh Walkenbach, Excel 2003 Power Programming with VBA Regards Darre -- apnda ----------------------------------------------------------------------- apndas's Profile: http://www.excelforum.com/member.php...fo&userid=3613 View this thread: http://www.excelforum.com/showthread.php?threadid=56654 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
All data is converted to #####. Why? | Excel Discussion (Misc queries) | |||
Automatically it should be converted | Excel Discussion (Misc queries) | |||
Help me with a pdf converted into excel | Excel Worksheet Functions | |||
Answer to 2/29 gets converted to 2/1 | Excel Discussion (Misc queries) | |||
How can a value be converted to words | Excel Discussion (Misc queries) |