Posted by Sean MacKenzie on Tue, Mar 30, 2010
In a world of increasing connectivity it makes no sense for any entity to use or enforce a date display that could be misinterpreted by others.
Ambiguous Dates
An ambiguous date format is one which requires a reader to reference some external source, such as a policy or standard in order to understand what real date the display is actually referring to. For example, if you looked at a report with the date
2/3/08 on it, you would need to find out which country it was written in, which standard was used for dates in the report, or even worse, guess what the author meant when using this format.
Examples
Food and drink producers in many countries are guilty of this practice. Consumers are often confounded by dates on products in formats they cannot decipher. For example, depending on what country you're in or which product you're about to eat, a date of
11 MA 10 on a canned good could mean
March 11th, 2010,
March 10th, 2011,
May 11th, 2010, or
May 10th, 2011! Make the wrong choice and you could be in trouble.
Businesses run into problems when dates are ambiguous, as they create scheduling mixups, customer order mixups, and more. The inefficiency caused by this issue can be significant.
Many governments and international bodies enforce their own versions of ambiguous date formats, which causes confusion. Even some international standards promote ambiguous dates such as the ISO 8601 dotted standard that would show February 3rd, 2008 as
03.02.2008 (this could be interpreted as March 2nd, 2008 in areas of North America using m/d/yy). ISO 8601 also uses a more logical format that shows the same date as
2008-02-03, which has less of a chance of misinterpretation since it appears to be in descending orders of magnitude (yyyy-mm-dd).
Use Unambiguous Dates!
What can you do? Well, you can help by changing peoples' attitudes in the workplace by discouraging the use of ambiguous date formats, and by challenging any standards or policies that enforce the use of ambiguous formats, since they are obsolete in a connected world and create organizational inefficiency.
It doesn't mean that everyone has to always use a long format (ex. February 9th, 2011), as there are popular short date formats like
dd-mmm-yyyy. This would be my first choice and would show the date as
09-Feb-2011. The day and month elements are known on first glance, and cannot be misinterpreted. My second choice would be for people to use the ISO 8601 standard
2011-02-09, which is logical and has month and day elements that would be guessed correctly by most people.
Posted by Sean MacKenzie on Mon, Feb 15, 2010
Spreadsheets are one of the most useful tools that businesses have today. Some people have become so proficient at using them that, as a result they use them all the time, for all kinds of things. This often leads into people using spreadsheets for purposes they weren't designed for. You would not use a spreadsheet when:
It needs to be shared concurrently.
When many others need to have access to view and update it, you need another tool. Excel allows one user at a time. This often leads to many people taking a copy of their own, which leads to a real mess when it all has to come back together.
You are continuously collecting data.
An engineer who has 50,000 rows and growing in an Excel sheet will soon hit the worksheet row limit of 65,536 rows. At that point she would have to figure out a way to continue.
The data needs to be secure.
Excel does not have data security in the same way an enterprise application does, and so can only protect data using some simple mechanisms.
The data needs to validated on entry.
Though it is possible to set up some data entry validation in excel, in practice, most people just pull up a sheet and start punching data into it. This can lead to errors in data-entry, making the data less accurate, and harder to run statistics on.
There is obviously another tool that would do it better.
There are examples of users creating letter templates, trying to maintain multi-user mission critical manufacturing schedules, trying to create graphics files with it, and more.
You're building a "database".
Many IT support staff find this to be one of the biggest misuses of Excel. Usually users spend a lot of time designing and entering data before they find their solution does not work. At this point the sheet is mission critical, so they ask their IT support staff to help them. It takes more time to analyze it, so time is wasted on both sides.
Ask for Help
If you find yourself in a position where you're considering using Excel for a project, consider some of the points above. If you fall into any of these categories, you may want to have a chat with your IT support staff or an IT specialist before continuing. You might find that a small amount of time invested with them up front will save you a large amount of grievance in the long run.
For example, they might be able to set up a simple SQL Server/MS Access database that everyone can use, has unlimited data entry, is more secure, and validates data on entry. As an added bonus, an MS Access front-end could spit out reports in Excel for analysis, a purpose Excel is more suited for.
Many thanks to people who contributed to a discussion on this topic at http://www.linkedin.com/answers/technology/information-technology/computers-software/TCH_ITS_CMP/625070-2600475
Posted by Sean MacKenzie on Tue, Feb 02, 2010
Microsoft discontinued support and enhancement of its Visual Basic 6 platform years ago, instead moving to its new .Net platform for enterprise applications. However, what many people don't know is that a flavour of VB called Visual Basic for Applications, or VBA as it is known, continued on in the Microsoft Office suite as the primary language for things like Excel and Word macros, and automation of Access database applications. It appears that, in spite of the relentless move toward .Net at Microsoft for many years, this technology will continue on and will be supported in Office 2010 (Office 14), and for SME business owners this is a very good thing.
1. Most businesses over 20 employees have invested in VBA-automated things for people "in the trenches", from simple mail merge applications in Word, to data extractions for analysis in Excel, to multi-user MS Access databases. Continuing support for these means that these companies will be able to extend the life of their investment.
2. Support for VBA also means that related technologies like the Jet database engine and Data Access Objects, or DAO can be supported by providers for years to come, on newer, more advanced platforms like Office 2010. For the large number of businesses who wrote entire systems around the MS Access/DAO/ODBC/SQL Server platform, this means they can stay current and be sure to be supported for years to come.
3. VBA with MS Access is regarded by many as the quickest way to prototype a working application for SME businesses due to a data access technology (DAO) tightly knit with the VBA language, a powerful design environment, and the best integration with other technologies in the office environment, like Word and Excel.
Ongoing VBA support means SME businesses can still get the best bang for their buck for years to come.