 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Weird date formatting
I am brand new to Excel and am having trouble formatting a date. We have different nationalities in the office where each person enters the date differently: * month, day, year * year, month, day * day month year Everyone defaults to their own country's way of doing things and we are getting very confused! To eliminate confusion, for example, I've formatted a cell "mmm-yy". But if someone enters "11-06" (for November 2006) the value becomes "Jun-06". Entering "9-07" (September 2007), it becomes Jul-06". If I enter "2007-8" it remains unchanged. The only time it works is if you actually enter the short-form month and year (Feb-07), which is kinda missing the point of formatting the cell to begin with. What is the best way around this? Get them to all format as dd-mmm-yyyy -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > I am brand new to Excel and am having trouble formatting a date. We have > different nationalities in the office where each person enters the date [quoted text clipped - 11 lines] > the point of formatting the cell to begin with. What is the best way around > this? Now, why didn't I think of that... The point of my post is that people are people, creatures of habit, and do not enter dates consistently. This is the reason programmers, etc. develop and use masks, automatic formatting, combo/list boxes, etc. to help ensure the integrity of data. > I am brand new to Excel and am having trouble formatting a date. We have > different nationalities in the office where each person enters the date [quoted text clipped - 11 lines] > the point of formatting the cell to begin with. What is the best way around > this? You won't get there with just formatting. Probably best is to use a form and echo the full date (02 August, 2002) and ask for confirmation. I know people have their own way of filling in dates, if they feel it doesn't matter. But I bet in your country too, you are not invited to enter license plate numbers, DOBs, passport numbers etc in any sequence you like.  SignatureKind regards, Niek Otten Microsoft MVP - Excel | Now, why didn't I think of that... The point of my post is that people are | people, creatures of habit, and do not enter dates consistently. This is the [quoted text clipped - 16 lines] | > the point of formatting the cell to begin with. What is the best way around | > this? > I am brand new to Excel and am having trouble formatting a date. We have > different nationalities in the office where each person enters the date [quoted text clipped - 11 lines] > the point of formatting the cell to begin with. What is the best way around > this? One possibility would be to provide a calendar to select from such as the one described here: http://www.ozgrid.com/VBA/excel-calendar-dates.htm gls858 I would try applying a Louisville Slugger against the temple of the defective keyboard operator! Sounds like time for boss to be boss, thats why He/She gets the big bucks. Lou > > I am brand new to Excel and am having trouble formatting a date. We have > > different nationalities in the office where each person enters the date [quoted text clipped - 17 lines] > > gls858 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.