Hi Paul: Thanks for this and happy holidays which ever one or two you do this time of year! I Think I'll try a combination of your #1 and still use volumes of acohol because there would be no fun it for me, otherwise.
regards, Michael On Dec 23, 4:04 pm, Paul Schreiner <[email protected]> wrote: > For this, I have to repeat my usual "diatribe" about Excel and "dates". > In Excel, there is not such thing as a date. > It's only a number. > The number of days since 1/1/1900. > In fact, there is no time. > time is merely the fractional part of a day. > Therefore, > Today, (12/23/2009) has been 40170 days since 1/1/1900. > and 10:51:13 AM is .4522300926 of a day, so: > 12-23-2009 10:51:13 AM = 40170.4522300926 > Excel provides the option of DISPLAYING this number > in a form that REPRESENTS (to our limited minds) a "date". > > Your problem is that in your "combo box", the day/month/year > is string of text, not really an actual "date". > (because we now know, there are no "dates") > > But when you paste it into a cell, Excel, in a fit of self-righteous power, > recognizes the format of the text string as a valid representation > of a date and converts it to a Number. > Then, whatever the default cell formatting is set to for "dates" > is applied to the number. > > You have (3) choices here: > (ok, there are probably LOTS more, but several involve the > use of large volumes of alcohol) > 1) insert a (') in the beginning of your text string when the combobox > contents are inserted into Excel. This will convert your "date string" > to an Excel string. The problem with this is that Excel will no longer > consider this value as a date, so future uses of this data is limited. > 2) Use Format Cells to change the display format to: dd/mm/yyyy > this can be done within VBA, or change the cell manually > 3) Go To Window control Panel and change the date formatting for your Windows > Operating System. This will ensure that all areas that Windows controls > (which includes most of the world and many small appliances) will display > in your selected format. > > hope this helps, > > Paul > > > > ----- Original Message ---- > > From:MD18358<[email protected]> > > To: MS EXCEL AND VBA MACROS <[email protected]> > > Sent: Wed, December 23, 2009 9:58:58 AM > > Subject: $$Excel-Macros$$ Month/Day/Year vs Day/Month/Year > > > I have a combo box pulling a list that is day/month/Year, however when > > the text in the combo is pushed back onto the spread sheet the dates > > are month/day/year. How can I make the listing consistant? > > > Mayby there is an example someone has that I can see the code. > > > Thanks > > Michael > > > Sorry, if this was asked before... > > > -- > > ---------------------------------------------------------------------------------- > > Some important links for excel users: > > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > >http://www.excelitems.com > > 2. Excel tutorials athttp://www.excel-macros.blogspot.com > > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com > > 4. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > To post to this group, send email to [email protected] > > If you find any spam message in the group, please send an email to: > > Ayush Jain @ [email protected] or > > Ashish Jain @ [email protected] > > <><><><><><><><><><><><><><><><><><><><><><> > > HELP US GROW !! > > > We reach over 6,500 subscribers worldwide and receive many nice notes about > > the > > learning and support from the group. Our goal is to have 10,000 subscribers > > by > > the end of 2009. Let friends and co-workers know they can subscribe to > > group at > >http://groups.google.com/group/excel-macros/subscribe- Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] If you find any spam message in the group, please send an email to: Ayush Jain @ [email protected] or Ashish Jain @ [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
